Fix the user/login sid mismatch when restoring a SQL Server Database on a different server.

When you take a backup of a SQL Server database one of the tables that is saved in the backup is called "sysusers". This table is used to link a server login to a database user. To do this, SQL Server links the "sid" field of the sysusers table to the "sid" field of the syslogins table in the master database.

select name, sid from sysusers

select name, sid from syslogins

When you restore the database backup on another SQL Server, the "sid" fields are out of sync and SQL Server does not fix this automatically. This is a big problem because your database users are pointing to logins that don't exist or worse, pointing to the wrong login. To fix this in a simple way you can run the following script:

   1:  DECLARE @username sysname

   2:   

   3:  SELECT top 1 @username = name 

   4:  FROM sysusers 

   5:  WHERE issqluser = 1 and (sid is not null and sid <> 0x0) and suser_sname(sid) is null 

   6:   

   7:   

   8:  WHILE @username is not null

   9:    BEGIN

  10:      IF @username='dbo'

  11:        BEGIN

  12:          PRINT N'mapped sa to the dbo user...'

  13:          EXEC sp_changedbowner 'sa' 

  14:        END

  15:      ELSE

  16:        BEGIN

  17:          PRINT N'mapped ' + @username + ' to the dbo user...'

  18:          EXEC sp_change_users_login 'update_one', @username, @username

  19:        END

  20:      SELECT @username = null

  21:      

  22:      SELECT TOP 1 @username = name 

  23:      FROM sysusers 

  24:      WHERE issqluser = 1 and (sid is not null and sid <> 0x0) and suser_sname(sid) is null 

  25:    END

The script simply tries to link the users to the logins based on the name, and it tries to make sa the default database user. Whew, problems fixed you would think... Not quite. You still have a bunch of users left in your database that are not linked to any logins. To remove this we can use the following script.

   1:  DECLARE @username sysname

   2:   

   3:  SELECT top 1 @username = su.name 

   4:  FROM sysusers su

   5:    left join master..syslogins sl

   6:    on su.name = sl.name

   7:  WHERE su.issqluser = 1 and (su.sid is not null and su.sid <> 0x0) 

   8:    and suser_sname(su.sid) is null  

   9:    and sl.name is null

  10:   

  11:  WHILE @username is not null

  12:    BEGIN

  13:      print N'Dropping user ' + @username + '...'

  14:      exec sp_dropuser @username

  15:   

  16:      SELECT @username = null

  17:      SELECT top 1 @username = su.name 

  18:      FROM sysusers su

  19:        left join master..syslogins sl

  20:        on su.name = sl.name

  21:      WHERE su.issqluser = 1 and (su.sid is not null and su.sid <> 0x0) 

  22:        and suser_sname(su.sid) is null  

  23:        and sl.name is null

  24:    END

To make using this code more practical I wrapped it in a stored procedure which you can install on the master database. You can download the script sp_fix_user_login_mismatch.sql or read the code below:

   1:  IF object_id('dbo.sp_fix_user_login_mismatch') IS NOT NULL

   2:    BEGIN

   3:      DROP PROCEDURE dbo.sp_fix_user_login_mismatch

   4:    END

   5:  GO

   6:  CREATE PROCEDURE dbo.sp_fix_user_login_mismatch

   7:  AS 

   8:   

   9:  DECLARE @username sysname

  10:   

  11:  -- Map all the database users to the logins that have the same name.

  12:  SELECT top 1 @username = su.name 

  13:  FROM sysusers su

  14:    inner join master..syslogins sl

  15:    on su.name = sl.name

  16:  WHERE su.issqluser = 1 and (su.sid is not null and su.sid <> 0x0) 

  17:    and suser_sname(su.sid) is null 

  18:   

  19:  WHILE @username is not null

  20:    BEGIN

  21:      IF @username='dbo'

  22:        BEGIN

  23:          PRINT N'mapped sa to the dbo user...'

  24:          EXEC sp_changedbowner 'sa' 

  25:        END

  26:      ELSE

  27:        BEGIN

  28:          PRINT N'mapped the login "' + @username + '" to the database user "' + @username + '"...'

  29:          EXEC sp_change_users_login 'update_one', @username, @username

  30:        END

  31:      SELECT @username = null

  32:      

  33:     SELECT top 1 @username = su.name 

  34:      FROM sysusers su

  35:        inner join master..syslogins sl

  36:        on su.name = sl.name

  37:      WHERE su.issqluser = 1 and (su.sid is not null and su.sid <> 0x0) 

  38:        and suser_sname(su.sid) is null 

  39:    END

  40:   

  41:  -- Remove all the database users that do not have a matching login.

  42:  SELECT top 1 @username = su.name 

  43:  FROM sysusers su

  44:    left join master..syslogins sl

  45:    on su.name = sl.name

  46:  WHERE su.issqluser = 1 and (su.sid is not null and su.sid <> 0x0) 

  47:    and suser_sname(su.sid) is null  

  48:    and sl.name is null

  49:   

  50:  WHILE @username is not null

  51:    BEGIN

  52:      print N'Dropping user ' + @username + '...'

  53:      exec sp_dropuser @username

  54:   

  55:      SELECT @username = null

  56:      SELECT top 1 @username = su.name 

  57:      FROM sysusers su

  58:        left join master..syslogins sl

  59:        on su.name = sl.name

  60:      WHERE su.issqluser = 1 and (su.sid is not null and su.sid <> 0x0) 

  61:        and suser_sname(su.sid) is null  

  62:        and sl.name is null

  63:    END

  64:  RETURN(0)

  65:  GO

I'm not a fan of giving you a procedure and leaving it to you to figure out if it is good code or not. So to unit test it I have used the following code

   1:   

   2:   

   3:  -- Test harness:

   4:  -- 

   5:  -- Create 3 logins to use as basis for our database users

   6:  -- Create a test database 

   7:  -- map the dbo owner to the fakesa login

   8:  -- add 2 users to the test database based on our 2 logins

   9:  -- backup the test database and drop it

  10:  -- drop fakesa and orphanedlogintest

  11:  -- restore the database and execute our fix procedure.

  12:  -- the user logintest should be remapped

  13:  -- the dbo user should be pointing to sa

  14:  -- the users fakesa and orphanedlogintest should be gone

  15:   

  16:   

  17:  set nocount on

  18:  create login fakesa WITH password = 'a D1ff1cult passw0rd'

  19:  create login logintest WITH password = 'a D1ff1cult passw0rd'

  20:  create login orphanedlogintest WITH password = 'a D1ff1cult passw0rd'

  21:  create database testloginproblems

  22:  GO

  23:  use testloginproblems

  24:  EXEC sp_changedbowner 'fakesa' 

  25:  exec sp_adduser 'logintest', 'logintest', 'db_datareader'

  26:  exec sp_adduser 'orphanedlogintest', 'orphanedlogintest', 'db_datareader'

  27:  use master

  28:  EXEC sp_addumpdevice 'disk', 'TestLoginProblemsData', 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\BACKUP\TestLoginProblemsData.bak'

  29:  backup database testloginproblems TO TestLoginProblemsData

  30:  drop database testloginproblems

  31:  drop login fakesa

  32:  drop login logintest

  33:  drop login orphanedlogintest

  34:  create login logintest WITH password = 'a D1ff1cult passw0rd'

  35:  RESTORE DATABASE testloginproblems FROM TestLoginProblemsData

  36:   

  37:  use testloginproblems

  38:  print N''

  39:  SELECT su.name as 'list of problematic users'

  40:  FROM sysusers su

  41:  WHERE su.issqluser = 1 and (su.sid is not null and su.sid <> 0x0) 

  42:    and suser_sname(su.sid) is null 

  43:   

  44:  exec sp_fix_user_login_mismatch

  45:   

  46:  print N''

  47:  SELECT ul.uname as 'user name to check', su.name as 'database user', sl.name 'server login'

  48:  FROM (select uname = 'logintest' union all select uname = 'dbo' union all select uname = 'fakesa' union all select uname = 'orphanedlogintest') ul

  49:    left join sysusers su

  50:    on ul.uname = su.name

  51:    left join master..syslogins sl

  52:    on su.sid = sl.sid

  53:   

  54:  use master  

  55:  EXEC sp_dropdevice 'TestLoginProblemsData', 'delfile'

  56:  drop database testloginproblems

  57:  drop login logintest