October 24, 2011 at 3:37 pm
Comments posted to this topic are about the item Fix orphaned users after restoring database to another server.
November 11, 2011 at 7:34 am
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
November 15, 2011 at 1:06 am
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" 😉
November 15, 2011 at 1:19 pm
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 --
November 15, 2011 at 1:56 pm
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.
November 15, 2011 at 2:03 pm
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.
November 15, 2011 at 2:55 pm
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
😛
November 15, 2011 at 7:38 pm
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" 😉
November 16, 2011 at 8:30 am
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.
November 16, 2011 at 8:40 am
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 --
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply