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
CREATE TABLE #GetListOfOrphanUser
rowid smallint IDENTITY(1,1),
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
SELECT @UserName = UserName
WHERE rowid = @NoOfUsers
SET @NoOfUsers = @NoOfUsers - 1
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.
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
DROP TABLE #GetListOfOrphanUser