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.