Fix orphaned users after restoring database to another server.

  • Vladimir Mednikov-324895

    SSC Enthusiast

    Points: 149

    Comments posted to this topic are about the item Fix orphaned users after restoring database to another server.

  • gurthang99

    SSC Veteran

    Points: 234

    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

  • Perry Whittle

    SSC Guru

    Points: 233859

    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" 😉

  • Mark D Powell

    SSCarpal Tunnel

    Points: 4453

    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 --

  • Vladimir Mednikov-324895

    SSC Enthusiast

    Points: 149

    Perry Whittle (11/15/2011)


    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.

    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.

  • Vladimir Mednikov-324895

    SSC Enthusiast

    Points: 149

    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.

  • jswong05

    Hall of Fame

    Points: 3503

    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
    😛

  • Perry Whittle

    SSC Guru

    Points: 233859

    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

    SSC Enthusiast

    Points: 149

    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.

  • Mark D Powell

    SSCarpal Tunnel

    Points: 4453

    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 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply