Preventing orphaned users in SQL Server 2008 R2

Question

I've recently had a situation where a database was refreshed from production to staging, and the users on the database ended up orphaned.

• These are sql server users, not mapped to windows credentials.
• The database users use the same credentials on both PRD and STG.
• The database users are called the same on both PRD and STG.
• The SQL Server logins are called the same on both PRD and STG.
• The database was refreshed by using a RESTORE with REPLACE and MOVE to rename the data files.

As I understand it, this is because the SID's of the logins don't match between our two environments.

How could I make it so that the SID's of the sql server logins on our staging environment are the same as on our production environment going forward?

My thought is to use CREATE LOGIN WITH SID ='SID value from PRD' but this has the disadvantages of needing to drop the logins first, and I'm not entirely sure that manually defining an SID is healthy / wise.

Maybe there is a way to have the logins migrated with the backup? (if sp_help_revlogin has been available to manually copy logins, why don't backups have this option)

Answer 1

As I understand it, this is because the SID's of the logins don't match between our two environments.

You understand correctly!

How could I make it so that the SID's of the sql server logins on our staging environment are the same as on our production environment going forward? My thought is to use CREATE LOGIN WITH SID ='SID value from PRD' but this has the disadvantages of needing to drop the logins first, and I'm not entirely sure that manually defining an SID is healthy / wise.

If the logins are already created, you'll have to drop them anyway - so that's a moot point. You can use the SSIS migrate logins task, sp_helprevlogin, CREATE LOGIN WITH SID =, and lastly you can make the database partially contained.

Maybe there is a way to have the logins migrated with the backup? (if sp_help_revlogin has been available to manually copy logins, why don't backups have this option)

Logins are a server scoped principal and can have a 1 to many relationship with users. Thus it doesn't make sense to include a server scope item with a database scoped item, that's why users exist. What you're looking for is called partially contained databases, which let a special type of user exist which has a passed and can be used like a login though it lives in the database and not at the server level. It's only good for that database and there are some requirements.

https://msdn.microsoft.com/en-us/library/ff929071.aspx