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
Regards
Vasudev Menon
Like us on FaceBook | Follow us on Twitter
Join the fastest growing SQL Server group on FaceBook
Hi Vasu,
Just a quick reply here.
sp_change_users_login stored porcesure is going to be depreceated in future versions which was suppose to happen from SQL 2008 but somehow still it still worksin SQL 2008.So it is better stop using sp_change_users_login.Instead use the following query to get a list of orphaned users
1 select p.name, sid from sys.database_principals p where p.sid not in (select sid from sys.server_principals) and type = ‘S’ and name’guest’
And use ‘Alter User’ command to fix the orphaned users.
1 Alter User OrphanedUser with login=ExistingLogin