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
MOVEto 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)