Linking ALL Users to Login after restoring a SQL Server 2005 database

(Note this question asks about linking ALL Users, unlike the possible duplicate that asks about linking a single user)

I wish to move a database between two servers, I have backed the database up from the first server and done a database restore on the 2nd server, so far so good.

However our application makes use of a lot of database users that are defined in the database. These have to be linked to logins that are defined in the master database. The server I have restored the database to has all the logins defined, however they have different sids.

I am not a T-SQL expert….

I think sp_change_users_login is part of the solution, but I can't find out how to get it to automatically link all users in the restored database to the login of the same name.

The database creation scripts we use for our application create the users and logins, however it does not specify the SID when creating the login, hence this problem. Now if I had a time machine...

(When I Google I get lots of hits, however they are mostly sites that won't let you see the answer without having to register on the site first.)

Solution 1:

Yes, you can do that by executing:

EXEC sp_change_users_login 'Auto_Fix' , 'TheUserName';

However if your question was can I fix all users automatically then this won't do that.

Solution 2:

I came up with the following. It works great because it shows you:

All the current orphaned users.
Which ones were fixed.
Which ones couldn't be fixed.

Other solutions require you to know the orphaned user name before hand in order to fix.

The following code could run in a sproc that is called after restoring a database to another server.

Script:

EXEC sp_change_users_login 'report'--See all orphaned users in the database.
DECLARE @OrphanedUsers TABLE
(
  IndexKey Int IDENTITY(1,1) PRIMARY KEY,
  UserName SysName,--nVarChar(128)
  UserSID  VarBinary(85)
)
INSERT INTO @OrphanedUsers
    EXEC sp_change_users_login 'report'

DECLARE @CRLF as nVarChar
    SET @CRLF = CHAR(10) + '&' + CHAR(13)--NOTE: Carriage-Return/Line-Feed will only appear in PRINT statements, not SELECT statements.
DECLARE @Sql as nVarChar(MAX)
    SET @Sql = N''
DECLARE @IndexKey as Int
    SET @IndexKey = 1
DECLARE @MaxIndexKey as Int
    SET @MaxIndexKey = (SELECT COUNT(*) FROM @OrphanedUsers)
DECLARE @Count as Int
    SET @Count = 0
DECLARE @UsersFixed as nVarChar(MAX)
    SET @UsersFixed = N''
DECLARE @UserName as SysName--This is an orphaned Database user.

WHILE (@IndexKey <= @MaxIndexKey)
  BEGIN
    SET @UserName = (SELECT UserName FROM @OrphanedUsers WHERE IndexKey = @IndexKey)
    IF 1 = (SELECT COUNT(*) FROM sys.server_principals WHERE Name = @UserName)--Look for a match in the Server Logins.
      BEGIN
        SET @Sql = @Sql + 'EXEC sp_change_users_login ''update_one'', [' + @UserName + '], [' + @UserName + ']' + @CRLF
        SET @UsersFixed = @UsersFixed + @UserName + ', '
        SET @Count = @Count + 1
      END
    SET @IndexKey = @IndexKey + 1
  END

PRINT @Sql
EXEC sp_executesql @Sql
PRINT   'Total fixed: ' + CAST(@Count as VarChar) + '.  Users Fixed: ' + @UsersFixed
SELECT ('Total fixed: ' + CAST(@Count as VarChar) + '.  Users Fixed: ' + @UsersFixed)[Fixed]
EXEC sp_change_users_login 'report'--See all orphaned users still in the database.

Result:

enter image description here

*Note: The 4 that were not fixed (in my example screenshot above) did not have a corresponding User in the destination Server that the database was restored to.

Solution 3:

If:

EXEC sp_change_users_login 'Auto_Fix' , 'TheUserName';

Doest't work, try this:

EXEC sp_change_users_login 'Auto_Fix', 'Username', NULL, 'p@ssword123'

I found it here: http://dbadiaries.com/using-sp_change_users_login-to-fix-sql-server-orphaned-users

Solution 4:

I found the following script from Microsoft KB918992 - run it on the original server and it will create a stored procedure called 'sp_help_revlogin' which generates another script to run on the destination server, creating all user accounts with the same passwords and sids. Worked wonders for our upgrade from SQL2000 to 2008.

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
    ELSE BEGIN -- SQL Server authentication
      IF (@binpwd IS NOT NULL)
      BEGIN -- Non-null password
        EXEC sp_hexadecimal @binpwd, @txtpwd OUT
        IF (@xstatus & 2048) = 2048
          SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
        ELSE
          SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
        PRINT @tmpstr
    EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
        SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name 
          + ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
      END
      ELSE BEGIN 
        -- Null password
    EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
        SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name 
          + ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
      END
      IF (@xstatus & 2048) = 2048
        -- login upgraded from 6.5
        SET @tmpstr = @tmpstr + '''skip_encryption_old''' 
      ELSE 
        SET @tmpstr = @tmpstr + '''skip_encryption'''
      PRINT @tmpstr 
    END
  END
  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
  END
CLOSE login_curs 
DEALLOCATE login_curs 
RETURN 0
GO

Solution 5:

List of all orphan user details with corresponding DB name

Simple step

    EXEC master.sys.sp_MSforeachdb ' USE [?]
    Select ''?'' 
      EXEC ?.dbo.sp_change_users_login   ''report'' '