04
May
04

Fix orphaned users in SQL Server


Every time I restore a database, I forget how to fix the orphaned users.  So I google, trying to find the magic words that will bring me to that stored procedure that I know exists, but I just can't remember the name of.  For those of you who don't know, sp_change_users_login has an 'Auto_Fix' action that will map a user in a database to a login of the same name. Since I'm going to forget all that again, I'm posting this little snippet of Books Online.  Maybe Google will help me find it next time!

sp_change_users_login

Changes the relationship between a Microsoft® SQL Server™ login and a SQL Server user in the current database.

Syntax

sp_change_users_login [ @Action = ] 'action'
    [ , [ @UserNamePattern = ] 'user' ]
    [ , [ @LoginName = ] 'login' ]

Arguments

[@Action =] 'action'

Describes the action to be performed by the procedure. action is varchar(10), and can be one of these values.

Value Description
Auto_Fix Links user entries in the sysusers table in the current database to logins of the same name in syslogins. It is recommended that the result from the Auto_Fix statement be checked to confirm that the links made are the intended outcome. Avoid using Auto_Fix in security-sensitive situations. Auto_Fix makes best estimates on links, possibly allowing a user more access permissions than intended.

user must be a valid user in the current database, and login must be NULL, a zero-length string (''), or not specified.

Report Lists the users, and their corresponding security identifiers (SID), that are in the current database, not linked to any login.

user and login must be NULL, a zero-length string (''), or not specified.

Update_One Links the specified user in the current database to login. login must already exist. user and login must be specified.



[@UserNamePattern =] 'user'

Is the name of a SQL Server user in the current database. user is sysname, with a default of NULL. sp_change_users_login can be used only with the security accounts of SQL Server logins and users; it cannot be used with Microsoft Windows NT® users.

[@LoginName =] 'login'

Is the name of a SQL Server login. login is sysname, with a default of NULL.

Return Code Values

0 (success) or 1 (failure)



4 Responses to “Fix orphaned users in SQL Server“


  1. #1 Ray Jezek 5.04.2004 at 10:13 PM

    Good stuff Ryan! This one definately comes in handy.



    Using roles helps mitigate this problem as well since they dont get "disconnected" when you restore.

  2. #2 http:// 5.20.2004 at 7:30 PM

    declare @usrname varchar(100), @command varchar(100)



    declare Crs insensitive cursor for



    select name as UserName from sysusers


    where issqluser = 1 and (sid is not null and sid <> 0x0)


    and suser_sname(sid) is null


    order by name



    for read only



    open Crs



    fetch next from Crs into @usrname


    while @@fetch_status=0



    begin



    select @command=' sp_change_users_login ''auto_fix'', '''+@usrname+''' '



    exec(@command)



    fetch next from Crs into @usrname



    end



    close Crs



    deallocate Crs






  3. #3 http:// 5.20.2004 at 7:31 PM

    USE master


    GO


    IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL


    DROP PROCEDURE sp_hexadecimal


    GO


    CREATE PROCEDURE sp_hexadecimal


    @binvalue varbinary(256),


    @hexvalue varchar(256) OUTPUT


    AS


    DECLARE @charvalue varchar(256)


    DECLARE @i int


    DECLARE @length int


    DECLARE @hexstring char(16)


    SELECT @charvalue = '0x'


    SELECT @i = 1


    SELECT @length = DATALENGTH (@binvalue)


    SELECT @hexstring = '0123456789ABCDEF'


    WHILE (@i <= @length)


    BEGIN


    DECLARE @tempint int


    DECLARE @firstint int


    DECLARE @secondint int


    SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))


    SELECT @firstint = FLOOR(@tempint/16)


    SELECT @secondint = @tempint - (@firstint*16)


    SELECT @charvalue = @charvalue +


    SUBSTRING(@hexstring, @firstint+1, 1) +


    SUBSTRING(@hexstring, @secondint+1, 1)


    SELECT @i = @i + 1


    END


    SELECT @hexvalue = @charvalue


    GO



    IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL


    DROP PROCEDURE sp_help_revlogin


    GO


    CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS


    DECLARE @name sysname


    DECLARE @xstatus int


    DECLARE @binpwd varbinary (256)


    DECLARE @txtpwd sysname


    DECLARE @tmpstr varchar (256)


    DECLARE @SID_varbinary varbinary(85)


    DECLARE @SID_string varchar(256)



    IF (@login_name IS NULL)


    DECLARE login_curs CURSOR FOR


    SELECT sid, name, xstatus, password FROM master..sysxlogins


    WHERE srvid IS NULL AND name <> 'sa'


    ELSE


    DECLARE login_curs CURSOR FOR


    SELECT sid, name, xstatus, password FROM master..sysxlogins


    WHERE srvid IS NULL AND name = @login_name


    OPEN login_curs


    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd


    IF (@@fetch_status = -1)


    BEGIN


    PRINT 'No login(s) found.'


    CLOSE login_curs


    DEALLOCATE login_curs


    RETURN -1


    END


    SET @tmpstr = '/* sp_help_revlogin script '


    PRINT @tmpstr


    SET @tmpstr = '** Generated '


    + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'


    PRINT @tmpstr


    PRINT ''


    PRINT 'DECLARE @pwd sysname'


    WHILE (@@fetch_status <> -1)


    BEGIN


    IF (@@fetch_status <> -2)


    BEGIN


    PRINT ''


    SET @tmpstr = '-- Login: ' + @name


    PRINT @tmpstr


    IF (@xstatus & 4) = 4


    BEGIN -- NT authenticated account/group


    IF (@xstatus & 1) = 1


    BEGIN -- NT login is denied access


    SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''


    PRINT @tmpstr


    END


    ELSE BEGIN -- NT login has access


    SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''


    PRINT @tmpstr


    END


    END

  4. #4 http:// 2.23.2005 at 9:41 PM

    I used the Stored Procs that Juan suggested recently. They worked to perfection. I highly recommend them.