Fix orphaned users after restoring database to another server.
-
Comments posted to this topic are about the item Fix orphaned users after restoring database to another server.
-
I hate cursors!
Tested on MS SQL 2008 R2
SET NOCOUNT ON
declare @GetListOfOrphanUser table
(
rowid smallint IDENTITY(1,1),
UserName sysname,
UserSID varbinary(85)
)
declare @UserName sysname
, @NoOfUsers smallint
-- To generate the orphaned users list.
INSERT @GetListOfOrphanUser (UserName, UserSID)
EXEC sp_change_users_login 'report'
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 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
-
Vladimir
I have not had chance to verify the code yet but the following is bothering me.
The temp table is populated by database users who have SUSER_NAME(SID) is null, this means the SQL server login does not exist! Because of this the 'update_one' action should fail.
For SQL server logins that do not exist either script them from the source or use the 'auto_fix' action.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs"
-
Nice, but this script has the same basic problem any fully automatic user re-sync script has, namely it assumes that the @UserNamePattern and @LoginName are the same. Just this morning I had to re-sync a vendor product database where at least one of the database users name varied from the instance user.
And I have at lease one other vendor application where the instance names are matched to different database names.
Just something to keep in mind and a reason to run report manually before running the automatic script and checking out the names.
-- Mark D Powell --
-
Perry Whittle (11/15/2011)
VladimirI have not had chance to verify the code yet but the following is bothering me.
The temp table is populated by database users who have SUSER_NAME(SID) is null, this means the SQL server login does not exist! Because of this the 'update_one' action should fail.
For SQL server logins that do not exist either script them from the source or use the 'auto_fix' action.
Good point, Perry.
This script doesn't replace all steps performed during the database transfer to another server. You can script missing logins and user roles on the source server and recreate them on the target server before fixing orphan users. Sometime it's not necessary especially in cases when you refresh development database form production and vice versus.
Anyway, while 'update_one' action fails on missing server logins the entire script won't.
-
Mark D Powell (11/15/2011)
Nice, but this script has the same basic problem any fully automatic user re-sync script has, namely it assumes that the @UserNamePattern and @LoginName are the same. Just this morning I had to re-sync a vendor product database where at least one of the database users name varied from the instance user.And I have at lease one other vendor application where the instance names are matched to different database names.
Just something to keep in mind and a reason to run report manually before running the automatic script and checking out the names.
-- Mark D Powell --
Mark,
I can only second it.
There is no "one click" procedure that could replace the entire transfer process. Assigning user names different from the corresponding server logins is a very bad practice, we definitely should "behave" our developers on this matter.
-
This stored procedure is deprecated
"sp_change_users_login".
I wrote a query to find all the privilege on the old database, then decide which needs to be implemented on the refreshed dev. It is half manual, but only a DBA can make judgement what should go over after a refresh.
Jason
http://dbace.us
-
Vladimir Mednikov-324895 (11/15/2011)
You can script missing logins and user roles on the source server and recreate them on the target server before fixing orphan users.Database roles are transferred with the database!
If you script the source logins and apply them to the target you have no orphaned logins!
Vladimir Mednikov-324895 (11/15/2011)
There is no "one click" procedure that could replace the entire transfer process.With a little imagination yes it could.
Vladimir Mednikov-324895 (11/15/2011)
Assigning user names different from the corresponding server logins is a very bad practice, we definitely should "behave" our developers on this matter.I don't agree it's a bad practice in fact the database user name should be disjointed from the server login for security!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs"
-
Vladimir Mednikov-324895 (11/15/2011)
Assigning user names different from the corresponding server logins is a very bad practice, we definitely should "behave" our developers on this matter.I don't agree it's a bad practice in fact the database user name should be disjointed from the server login for security!
Good for security, not good for the poor DBA's head.
-
I think Perry has a point about security. I was looking at this yesterday and it would appear that you can join the database sysusers to the instance server_principals and find the matching names for each valid sid then use this information to generate the script to re-sync the usernames on the new system.
Unfortunately, this has to be done prior to the restore to the new instance or requires that the source database still be available to you but it does provide a method to duplicate the origional linkages.
While looking at this I found a database with users defined to it that do not show up im EM or SSMS. The name started with a backslash and I got sidetracked trying to figure out what type of enties these were.
HTH -- Mark D Powell --
-
Find below script to fix the orphan users in SQL database.
DECLARE @UserName nvarchar(255)
DECLARE orphanuser_cur cursor for
SELECT UserName = name
FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0x0) and
suser_sname(sid) is null
ORDER BY name
OPEN orphanuser_cur
FETCH NEXT FROM orphanuser_cur INTO @UserName
WHILE (@@fetch_status = 0)
BEGIN
PRINT @UserName + ' user name being resynced'
EXEC sp_change_users_login 'Update_one', @UserName, @UserName
FETCH NEXT FROM orphanuser_cur INTO @UserName
END
CLOSE orphanuser_cur
DEALLOCATE orphanuser_cur
-
don’t use sysusers and sp_change_users_login, they’re deprecated and will be removed, also don’t use a cursor.
use the ALTER USER command to fix orphaned database users
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs"
-
Thank you for suggestion. Could you please share the script if you have . That will help for me.
Thank you in Advanced !!
-
Take this as a base to identify orphaned windows and instance logins, then wrap around to create statements required
select dp.name, sp.name
from sys.database_principals dp
LEFT OUTER JOIN sys.server_principals sp
ON dp.sid = sp.sid
where dp.authentication_type in (1,3)
AND dp.type <> 'R' AND sp.name IS NULL-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs"
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply