How to handle users and logins in Visual Studio Database Project?

Question

I've built a database in SQL Server 2008 R2 and am using Visual Studio 2010 Ultimate to create a database project for it.

I've created both a SQL Server project and Database project to represent my environment based on this MSDN walkthrough. The schema comparisons for both projects work as expected and I'm able to replicate all changes server to project.

However, it seems to have imported some environment specific configuration, such as logins, user/login mapping, local service accounts (e.g. NT SERVICE\MSSQL$SQLEXPRESS2008), etc. This seems not ideal because my impression was this database project could be deployed onto another server and those environment specific details could be different. For example the named instances could be different between Dev, QA, and Prod. Users could be mapped to different logins.

Am I doing this properly or should I be taking extra steps to make the database project portable to all environments?

My goal is to utilize this project to

1. Capture schema in version control
2. deploy schema changes such as by generating change scripts
3. tie to unit testing to create a disposable test environment on the fly.

Answer 1

We've had to mess with this before and ended up using Jamie Thompson's idea of creating post-deployment scripts to handle permissions based on a variable containing the name of the environment/configuration. You can find the (archived) article here: https://web.archive.org/web/20190222004817/http://sqlblog.com/blogs/jamie_thomson/archive/2010/07/21/a-strategy-for-managing-security-for-different-environments-using-the-database-development-tools-in-visual-studio-2010.aspx

Note: Jamie's link is apparently dead. I wrote up something based on it here: http://schottsql.com/2013/05/14/ssdt-setting-different-permissions-per-environment/

I also wrote a script to handle scripting of permissions:

SELECT
state_desc + ' ' + permission_name +
' on ['+ ss.name + '].[' + so.name + ']
to [' + sdpr.name + ']'
COLLATE LATIN1_General_CI_AS as [Permissions T-SQL]
FROM SYS.DATABASE_PERMISSIONS AS sdp
JOIN sys.objects AS so
     ON sdp.major_id = so.OBJECT_ID
JOIN SYS.SCHEMAS AS ss
     ON so.SCHEMA_ID = ss.SCHEMA_ID
JOIN SYS.DATABASE_PRINCIPALS AS sdpr
     ON sdp.grantee_principal_id = sdpr.principal_id

UNION

SELECT
state_desc + ' ' + permission_name +
' on Schema::['+ ss.name + ']
to [' + sdpr.name + ']'
COLLATE LATIN1_General_CI_AS as [Permissions T-SQL]
FROM SYS.DATABASE_PERMISSIONS AS sdp
JOIN SYS.SCHEMAS AS ss
     ON sdp.major_id = ss.SCHEMA_ID
     AND sdp.class_desc = 'Schema'
JOIN SYS.DATABASE_PRINCIPALS AS sdpr
     ON sdp.grantee_principal_id = sdpr.principal_id
order by [Permissions T-SQL]
GO

Together, I set up the permissions into post-deploy scripts and folders that will recreate users/roles/permissions based on the environment. We call a "wrapper" script from the main post-deploy section that goes through the rest to figure out which section to run.

Answer 2

Here is how our agency adds the Users, updates the roles and data object permissions to a database project using Visual Studio 2022 when deploying permissions to different environments. First we run a script in each environment against the database.

USE [databasename]   --  <-- Add the database you are working on.script by dougp
GO
DECLARE @sql VARCHAR(2048)
      , @sort INT
      , @sort2 INT
    , @env varchar(10)
    , @rolesql varchar(1000)
    , @filename varchar(150)

set @env = 'DEV'       --  <-- Change this to match the environment you are working on.

declare @excludeUser table (
  username varchar(128)

)
-- use the following to exclude users that are no longer with your organization, but may still have rights to the database
insert @excludeUser
values ('RetiredEmployee')
     , ('DismissedContractor')



DECLARE tmp CURSOR FOR


SELECT '
-- [-- PostDeployment.sql --] --' AS [-- SQL STATEMENTS --],
1 AS [-- RESULT ORDER HOLDER --],
0 AS [-- FILE ORDER HOLDER --]

UNION
SELECT '/*
Post-Deployment Script Template             
--------------------------------------------------------------------------------------
 This file contains SQL statements that will be appended to the build script.   
 Use SQLCMD syntax to include a file in the post-deployment script.     
 Example:      :r .\myfile.sql                
 Use SQLCMD syntax to reference a variable in the post-deployment script.   
 Example:      :setvar TableName MyTable              
               SELECT * FROM [$(TableName)]         
--------------------------------------------------------------------------------------
*/

:r Security/CreateUserConnection.sql
:r Security/AlterRole.sql
:r Security/DataObjectPermission.sql',
2 AS [-- RESULT ORDER HOLDER --],
0 AS [-- FILE ORDER HOLDER --]



UNION
SELECT '
-- [-- CreateUserConnection.sql --] --' AS [-- SQL STATEMENTS --],
1 AS [-- RESULT ORDER HOLDER --],
1 AS [-- FILE ORDER HOLDER --]

UNION
SELECT 'IF (''$(ReleaseEnvironment)'' = ''' + @env + ''')',
2 AS [-- RESULT ORDER HOLDER --],
1 AS [-- FILE ORDER HOLDER --]

UNION
SELECT 'BEGIN
' AS [-- SQL STATEMENTS --],
3 AS [-- RESULT ORDER HOLDER --],
1 AS [-- FILE ORDER HOLDER --]

UNION
SELECT  '    IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = ' + SPACE(1) + '''' + dp.[name] + '''' + ') CREATE USER ' + SPACE(1) + QUOTENAME(dp.[name]) + ' FOR LOGIN ' + QUOTENAME(l.[name])
  + CASE
    WHEN dp.[default_schema_name] IS NULL
      THEN ''
    ELSE ' WITH DEFAULT_SCHEMA = ' + QUOTENAME(dp.[default_schema_name]) + SPACE(1) 
    END + '; ' AS [-- SQL STATEMENTS --],
5 AS [-- RESULT ORDER HOLDER --],
1 AS [-- FILE ORDER HOLDER --]
FROM  sys.database_principals dp  --SELECT type, type_desc FROM sys.database_principals group by type, type_desc
                                    --C CERTIFICATE_MAPPED_USER
                                    --G WINDOWS_GROUP
                                    --R DATABASE_ROLE
                                    --S SQL_USER
                                    --U WINDOWS_USER
  INNER JOIN sys.syslogins l ON l.sid = dp.sid
WHERE dp.[type] IN ('U', 'S', 'G') -- windows users, sql users, windows groups
  AND dp.[name] NOT IN (select username from @excludeUser)
  AND dp.[principal_id] > 4 -- 0 to 4 are system users/schemas

UNION
SELECT '' AS [-- SQL STATEMENTS --],
6 AS [-- RESULT ORDER HOLDER --],
1 AS [-- FILE ORDER HOLDER --]

UNION
SELECT CASE 
            WHEN perm.state <> 'W' THEN '    ' + perm.state_desc --W=Grant With Grant Option
            ELSE '    GRANT'
        END
    + SPACE(1) + perm.permission_name --CONNECT, etc
    + SPACE(1) + 'TO' + SPACE(1) + '[' + USER_NAME(usr.principal_id) + ']' COLLATE database_default --TO <user name>
    + CASE 
            WHEN perm.state <> 'W' THEN SPACE(0) 
            ELSE SPACE(1) + 'WITH GRANT OPTION' 
      END
        AS [-- SQL STATEMENTS --],
7 AS [-- RESULT ORDER HOLDER --],
1 AS [-- FILE ORDER HOLDER --]
FROM sys.database_permissions perm
  INNER JOIN sys.database_principals usr ON perm.grantee_principal_id = usr.principal_id
--WHERE usr.name = @OldUser
WHERE [perm].[major_id] = 0
  AND [usr].[principal_id] > 4 -- 0 to 4 are system users/schemas
  AND [usr].[type] IN ('G', 'S', 'U') -- S = SQL user, U = Windows user, G = Windows group
  AND USER_NAME(usr.principal_id) NOT IN (select username from @excludeUser)
  and exists (select sid from sys.syslogins where sid = usr.sid)

UNION
SELECT 'END;' AS [-- SQL STATEMENTS --],
8 AS [-- RESULT ORDER HOLDER --],
1 AS [-- FILE ORDER HOLDER --]




UNION
SELECT '
-- [-- AlterRole.sql --] --' AS [-- SQL STATEMENTS --],
1 AS [-- RESULT ORDER HOLDER --],
2 AS [-- FILE ORDER HOLDER --]

UNION
SELECT 'IF (''$(ReleaseEnvironment)'' = ''' + @env + ''')',
2 AS [-- RESULT ORDER HOLDER --],
2 AS [-- FILE ORDER HOLDER --]

UNION
SELECT 'BEGIN
' AS [-- SQL STATEMENTS --],
3 AS [-- RESULT ORDER HOLDER --],
2 AS [-- FILE ORDER HOLDER --]

UNION
SELECT  '    ALTER ROLE [' + USER_NAME(rm.role_principal_id) + '] ADD MEMBER [' + USER_NAME(rm.member_principal_id) + '];' AS [-- SQL STATEMENTS --],
5 AS [-- RESULT ORDER HOLDER --],
2 AS [-- FILE ORDER HOLDER --]
FROM  sys.database_role_members rm
WHERE USER_NAME(rm.member_principal_id) IN (  
                                             --get user names on the database
                                             SELECT dp.[name]
                                             FROM sys.database_principals dp
                                             WHERE dp.[principal_id] > 4 -- 0 to 4 are system users/schemas
                                               and dp.[type] IN ('G', 'S', 'U','R') -- G=WINDOWS_GROUP,S=SQL_USER,U=WINDOWS_USER,R=DATABASE_ROLE
                                               and ((exists (select sid from sys.syslogins where sid = dp.sid)) or (dp.[type] IN ('R'))))
                                            
  AND USER_NAME(rm.member_principal_id) NOT IN (select username from @excludeUser)
--ORDER BY rm.role_principal_id ASC

UNION
SELECT 'END;' AS [-- SQL STATEMENTS --],
7 AS [-- RESULT ORDER HOLDER --],
2 AS [-- FILE ORDER HOLDER --]




UNION
SELECT '
-- [-- DataObjectPermission.sql --] --' AS [-- SQL STATEMENTS --],
1 AS [-- RESULT ORDER HOLDER --],
3 AS [-- FILE ORDER HOLDER --]

UNION
SELECT 'IF (''$(ReleaseEnvironment)'' = ''' + @env + ''')',
2 AS [-- RESULT ORDER HOLDER --],
3 AS [-- FILE ORDER HOLDER --]

UNION
SELECT 'BEGIN
' AS [-- SQL STATEMENTS --],
3 AS [-- RESULT ORDER HOLDER --],
3 AS [-- FILE ORDER HOLDER --]

UNION
SELECT CASE 
         WHEN perm.state <> 'W' THEN '    ' + perm.state_desc 
         ELSE '    GRANT'
       END
       + SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(SCHEMA_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name) --select, execute, etc on specific objects
       + CASE
           WHEN cl.column_id IS NULL THEN SPACE(0)
           ELSE '(' + QUOTENAME(cl.name) + ')'
         END
        + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default
        + CASE 
            WHEN perm.state <> 'W' THEN SPACE(0)
            ELSE SPACE(1) + 'WITH GRANT OPTION'
          END AS [-- SQL STATEMENTS --],
5 AS [-- RESULT ORDER HOLDER --],
3 AS [-- FILE ORDER HOLDER --]
FROM  sys.database_permissions perm
  INNER JOIN sys.objects obj ON perm.major_id = obj.[object_id]
  INNER JOIN sys.database_principals usr ON perm.grantee_principal_id = usr.principal_id
  LEFT  JOIN sys.columns cl ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
WHERE obj.[name] not like 'dt%'
  and (
       exists (select sid from sys.syslogins where sid = usr.sid)
    or usr.type not in ('S', 'U', 'G')--S=SQL_USER, U=WINDOWS_USER, G=WINDOWS_GROUP
  )
--WHERE usr.name = @OldUser
--ORDER BY perm.permission_name ASC, perm.state_desc ASC



UNION
SELECT '' AS [-- SQL STATEMENTS --],
6 AS [-- RESULT ORDER HOLDER --],
3 AS [-- FILE ORDER HOLDER --]

UNION
SELECT  CASE
            WHEN perm.state <> 'W' THEN '    ' + perm.state_desc --W=Grant With Grant Option
            ELSE '    GRANT'
            END
                + SPACE(1) + perm.permission_name --CONNECT, etc
                + SPACE(1) + 'ON' + SPACE(1) + perm.class_desc + '::' COLLATE database_default --TO <user name>
                + QUOTENAME(SCHEMA_NAME(perm.major_id))
                + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(perm.grantee_principal_id)) COLLATE database_default
                + CASE
                    WHEN perm.state <> 'W' THEN SPACE(0)
                    ELSE SPACE(1) + 'WITH GRANT OPTION'
                    END
            AS [-- SQL STATEMENTS --],
8 AS [-- RESULT ORDER HOLDER --],
3 AS [-- FILE ORDER HOLDER --]
FROM sys.database_permissions AS perm
  INNER JOIN sys.schemas s on perm.major_id = s.schema_id
  INNER JOIN sys.database_principals dbprin on perm.grantee_principal_id = dbprin.principal_id
WHERE perm.class = 3 --class 3 = schema
  and (
       exists (select sid from sys.syslogins where sid = dbprin.sid)
    or dbprin.type not in ('S', 'U', 'G')--S=SQL_USER, U=WINDOWS_USER, G=WINDOWS_GROUP
  )

UNION
SELECT 'END;' AS [-- SQL STATEMENTS --],
9 AS [-- RESULT ORDER HOLDER --],
3 AS [-- FILE ORDER HOLDER --]


ORDER BY [-- FILE ORDER HOLDER --],
[-- RESULT ORDER HOLDER --]


OPEN tmp
FETCH NEXT FROM tmp INTO @sql, @sort, @sort2
WHILE @@FETCH_STATUS = 0
BEGIN
  PRINT @sql
  FETCH NEXT FROM tmp INTO @sql, @sort, @sort2  
END

CLOSE tmp
DEALLOCATE tmp




IF @env = 'PROD'
BEGIN

PRINT '


-- [-- Security/Roles --] --'

DECLARE tmp CURSOR FOR

SELECT '
-- [ -- ' + r.[name] + '.sql -- ] --' AS 'file_name'
, 'CREATE ROLE [' + r.[name] + ']
  AUTHORIZATION [' + o.[name] + '];
GO

' AS 'sql_code'
FROM sys.database_principals r
  INNER JOIN sys.database_principals o ON o.principal_id = r.owning_principal_id
WHERE r.type = 'R'
  AND r.[name] <> 'public'
  AND r.[name] NOT LIKE 'db[_]%'
  and (
       exists (select sid from sys.syslogins where sid = o.sid)
    or o.type not in ('S', 'U', 'G')--S=SQL_USER, U=WINDOWS_USER, G=WINDOWS_GROUP
  )
ORDER BY r.[name]
  
OPEN tmp
FETCH NEXT FROM tmp INTO @filename, @rolesql
WHILE @@FETCH_STATUS = 0
BEGIN
  PRINT @filename
  PRINT @rolesql
  FETCH NEXT FROM tmp INTO @filename, @rolesql  
END

CLOSE tmp
DEALLOCATE tmp

PRINT '


-- [-- Security/Schemas --] --'

DECLARE tmp CURSOR FOR
  
SELECT distinct '
-- [ -- ' + s.[name] + '.sql -- ] --' AS 'file_name'
, 'CREATE SCHEMA [' + s.[name] + ']
  AUTHORIZATION [' + p.[name] + '];
GO

' AS 'sql_code'
FROM sys.schemas s
  INNER JOIN sys.schemas p on p.schema_id = s.principal_id
  INNER JOIN sys.sysobjects o on o.uid = s.schema_id
WHERE s.[name] not in ('sys', 'dbo')
ORDER BY 1

  
OPEN tmp
FETCH NEXT FROM tmp INTO @filename, @rolesql
WHILE @@FETCH_STATUS = 0
BEGIN
  PRINT @filename
  PRINT @rolesql
  FETCH NEXT FROM tmp INTO @filename, @rolesql  
END

CLOSE tmp
DEALLOCATE tmp

END

If you don’t have a Security folder create one. Create folders if necessary for your roles/schemas. Create roles or schemas individually and add to corresponding folders. Roles and schema need to be part of the build.

Add 3 new scripts to the Security folder: CreateUserConnection.sql, AlterRole.sql and DataObjectPermission.sql Do not include these scripts in the BUILD: [VSSolutionExplorerSecurityTreeFolder][1] Take the scripts from each environment and combine them into their corresponding files.

-- [-- CreateUserConnection.sql --] – illustrating how to add different user to different environments, create each script as below ( CreateUserConnection.sql, AlterRole.sql and DataObjectPermission.sql )
IF ('$(ReleaseEnvironment)' = 'DEV')
BEGIN
    IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] =  'InfoStagingLSRReader') CREATE USER  [InfoStagingLSRReader] FOR LOGIN [InfoStagingLSRReader] WITH DEFAULT_SCHEMA = [dbo] ; 
    IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] =  'InfoStagingLSRWriter') CREATE USER  [InfoStagingLSRWriter] FOR LOGIN [InfoStagingLSRWriter] WITH DEFAULT_SCHEMA = [dbo] ; 
    GRANT CONNECT TO [InfoStagingLSRReader]
    GRANT CONNECT TO [InfoStagingLSRWriter]
END;

IF ('$(ReleaseEnvironment)' = 'QA')
BEGIN
    IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] =  'InfoLSRReader') CREATE USER  [InfoLSRReader] FOR LOGIN [InfoLSRReader] WITH DEFAULT_SCHEMA = [dbo] ; 
    IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] =  'InfoLSRWriter') CREATE USER  [InfoLSRWriter] FOR LOGIN [InfoLSRWriter] WITH DEFAULT_SCHEMA = [dbo] ; 
    GRANT CONNECT TO [InfoLSRReader]
    GRANT CONNECT TO [InfoLSRWriter]
END;

IF ('$(ReleaseEnvironment)' = 'PROD')
BEGIN
    IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] =  'LSRReader') CREATE USER  [LSRReader] FOR LOGIN [LSRReader] WITH DEFAULT_SCHEMA = [dbo] ; 
    IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] =  'LSRWriter') CREATE USER  [LSRWriter] FOR LOGIN [LSRWriter] WITH DEFAULT_SCHEMA = [dbo] ; 
    GRANT CONNECT TO [LSRReader]
    GRANT CONNECT TO [LSRWriter]
END;

Next add a post deployment script and add the following:

:r Security/CreateUserConnection.sql
:r Security/AlterRole.sql
:r Security/DataObjectPermission.sql

Add the SQLCMD variables for the different environments – since I have named my variable to $(ReleaseEnvironment) here is what it will look like: [SQLCMDVariables][2]

I couldn't find too much on security, so wanted to share. Hope this helps.
[1]: https://i.stack.imgur.com/MrPt1.png [2]: https://i.stack.imgur.com/3Ax5h.png