How To Run as Normal User

  • Article

Can you use Visual Studio Team Edition for Database Professionals running as a normal Windows user? Yes!

You have to provision the Windows user with the appropriate rights and privileges on the local SQL Server instance that is used for design time validation. In order for a normal Windows users to use Visual Studio Team Edition for Database Professionals you need to have the following rights on the local SQL Server instance:

  1. The user needs a SQL Server login:

    CREATE LOGIN [$(USERDOMAIN)\$(USERNAME)] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

  2. The SQL Server login needs to be a member of the build-in dbcreator role:

    EXEC master..sp_addsrvrolemember @loginame = N'$(USERDOMAIN)\$(USERNAME)', @rolename = N'dbcreator'

  3. The SQL Server login needs to be a member of the build-in securityadmin role:

    EXEC master..sp_addsrvrolemember @loginame = N'$(USERDOMAIN)\$(USERNAME)', @rolename = N'securityadmin'

  4. And the SQL Server login needs to have the VIEW SERVER STATE privilege granted:

    GRANT VIEW SERVER STATE TO [$(USERDOMAIN)\$(USERNAME)]

To check if you gave the user the appropriate rights you can run the following query which has to return 1 if you done it right.

SELECT 1
FROM fn_my_permissions(NULL, N'server')
WHERE [permission_name] = N'VIEW SERVER STATE'
AND IS_SRVROLEMEMBER(N'dbcreator') = 1
AND IS_SRVROLEMEMBER(N'securityadmin') = 1

Now there is one more thing!

If you have SQL Server 2005 SP1 installed you need to grant the public group execute rights to the sp_detach_db procedure. Why? In the RTM version of SQL Server 2005 this was already done for you, but when you installed SQL Server 2005 SP1, this grant was dropped by accident, so to get you back to the original state you need to run the following query:

GRANT EXECUTE ON [sp_detach_db] TO [public]

Why public, am I opening up the world? No, the actual validation is done inside the procedure, so granting access to public is not providing this right to the whole world access to this procedure. 

Now you should be able to login as a normal Windows user and use Visual Studio Team Edition for Database Professionals.

-GertD