Today, I was again requested for peer support from one of our DBA team mates who is racing against time to complete the TESTING environment refresh with LIVE data.
As part of his task remit, he is currently refreshing TEST SQL instance(s) [more that 10 SQL instances, with per instance averaging 15+ databases] with LIVE data, meaning he is restoring LIVE databases onto TEST SQL instances.
As part of this, after the restores, he wanted to map security users and logins to various roles. Meaning after the restore, ensuring the security mappings are re-set to “point-in-time before the restores” happened.
The whole exercise of carrying out a TESTING environment refresh, is to give the development team an exact representation of LIVE data for testing purposes and hence the security mappings of TESTING environment is definitely going to different from that of LIVE production.
He soon realized that there were orphaned users on majority of the databases and he could not find the script to fix the users from our normal script repository which the DBA team uses for their operational activities.
NOTE: For beginners, an orphaned user is a user in a SQL Server database that is not associated with a SQL Server login. Orphaned users are created when a database backup from one SQL instance is restored on another SQL instance.
The below code was provided to him to help fix the orphaned user, per database per instance, meaning he has to run the code on all the restored databases. The code also contains comments on how to list out orphaned users, how to fix orphaned users one by one OR how to auto fix orphaned users.
SET NOCOUNT ON BEGIN CREATE TABLE #GetListOfOrphanUser ( rowid smallint IDENTITY(1,1), UserName sysname, UserSID varbinary(85) ) declare @UserName sysname declare @NoOfUsers smallint INSERT #GetListOfOrphanUser (UserName, UserSID) EXEC sp_change_users_login 'report' -- To generate the orphaned users list. /* To fix the orphaned users at a single stroke, can also use the below command EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'passsword'. But in case for an orphaned user, if an equivalent login does not exist then it will create a login id and password. Not a requirement for our development SQL estate environment. */ SET @NoOfUsers = @@ROWCOUNT WHILE @NoOfUsers > 0 BEGIN SELECT @UserName = UserName FROM #GetListOfOrphanUser WHERE rowid = @NoOfUsers SET @NoOfUsers = @NoOfUsers - 1 BEGIN TRY EXEC sp_change_users_login 'Update_One', @UserName, @UserName /* In development our SQL estate environment, orphaned user needs to be fixed if and only if the corresponding login exists. */ END TRY BEGIN CATCH /* Nothing to do incase the logins for equivalent users does not exist. Over here, it can customised to remove the orphaned user in case equivalent login does not exist */ END CATCH END DROP TABLE #GetListOfOrphanUser END