SQL Server – Preserving Existing Logins During Database Refresh

loginssql serversql server 2014

I am trying to do database refresh within Sql server 2014 instances from lower env to production for making it to golive. All the required logins exist on current prod and as far as I understand while doing db refresh they will be overwritten or replaced. I know there is sp_helprevlogin but to script out from current prod and then recreate which seems time consuming activity in prod. Just looking if there is a better way i can still preserve those logins or users from getting overwritten?

Update@ Also what about the database roles?

Best Answer

Not sure how you're doing your DB refresh. A DB restore of a user database doesn't change the logins; that should only be an issue if you are replacing the master DB, which I wouldn't generally recommend.

However, if the SID of a login in prod doesn't match the SID of that same login in the source environment, then the users may not match up correctly; that's when you'd have to deal with "orphaned" users in your databases.

As you note, scripting out the users in one environment, and loading them to another, should ensure that the SIDs in both environments line up correctly. That said, unless you're doing this when first setting up a server, I wouldn't try this.

Microsoft has an article that helps you troubleshoot orphaned users for SQL Server 2014.

To find orphaned users, run the following:

USE <database_name>;  
GO
sp_change_users_login @Action='Report';  
GO

This will identify users tied to a login SID that doesn't exist.

You can fix these with the following statement:

USE <database_name>;  
GO  
sp_change_users_login @Action='update_one', @UserNamePattern='<database_user>', @LoginName='<login_name>';  
GO

This updates the login SID for the user, so it matches that of the login you specify.

NOTE: the above works (as best I recall) going back at least as far as SQL Server 2008. However, the process has changed for SQL Server 2016 & 2017. Here's a link to the SQL 2016 version of the page I linked to above.

Related Question