0

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?

1

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.

| improve this answer | |
  • Repair-DbaOrphanUser also makes taking care of this super easy. – LowlyDBA Jun 21 '18 at 22:02
  • @RDFozz, thanks and what about the database roles created? – BeginnerDBA Jun 22 '18 at 14:07
  • Database roles are assigned to users. This doesn't drop and recreate the users, it just attached them to a login. So, database roles will already be assigned, and shouldn't be affected. Again, this is assuming your "refresh" involves restoring a DB backup. – RDFozz Jun 22 '18 at 14:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.