After moving database (backup, restore), I have to re-add user

Question

I occasionally move a database (SQL Express 2012) from a development machine to a server, or vice versa, using database backup and restore in SQL Server Management Studio.

Whenever I do this, the applications on the target machine cannot access the database until I delete the user they use, "george", from the database users (Database, Security, Users in SQL Server Management Studio), and re-add it as owner under Security, Logins, george/properties, user mapping.

Is there a better way to do this? It seems a little convoluted.

• Jon Seigel: The SIDs of the logins don't match between the servers. Manually specify the SID in the CREATE LOGIN statement.

Answer 1

This is the difference between logins and users and how they relate to each other:

• Logins - Instance level principals that allow an entity to connect to the SQL Server instance. They do not, by their nature, grant any access to databases on the instance. The exception to this is a login with sysadmin rights can use a database because they are sysadmin, but because of sysadmin level permissions.
• Users - Database level principals that allow an entity to connect to a SQL Server database. Users are associated with logins via SIDs, creating a relationship between the two and allowing a login to connect to the instance and then use the associated user to connect to the database.

What commonly happens with SQL authenticated logins and database users on a restore is that the SIDS will be out of sync, thus breaking the relationship. This relationship must be repaired before you can connect to the database using that login, because in the eyes of SQL Server those principals are no longer connected. You can fix this with the following SQL:

ALTER USER [foo] WITH LOGIN=[foo]

You can use the following query in the context of your database to check for orphans:

select
 dp.name [user_name]
 ,dp.type_desc [user_type]
 ,isnull(sp.name,'Orhphaned!') [login_name]
 ,sp.type_desc [login_type]
from 
 sys.database_principals dp
 left join sys.server_principals sp on (dp.sid = sp.sid)
where
 dp.type in ('S','U','G')
 and dp.principal_id >4
order by sp.name

• Tomas: I get error "Windows NT user or group 'ca-v2-staging' not found. Check the name again." when run your first query, the seconds query works fine and return lost sql user "ca-v2-staging".

Answer 2

Here is a solution that worked for me. What it does is to:

1. List orphaned users: EXEC sp_change_users_login 'REPORT'
2. Fix the users: EXEC sp_change_users_login 'UPDATE_ONE','<userName>','<userName>'

Answer 3

You could look into changing the database to contained database. Contained database user are authenticated by the database, not at instance level through login. It makes moving database to different instance simpler.

If not, you could backup the login information using sp_help_revlogin scripts provided at this Microsoft support KB. And execute the output script on the new instance.

Answer 4

I use the script below to migrate/create the user accounts and logins. Run it from the Server that you have restored the database to and supply the original Server name as a parameter to the procedure.

I don't take any credit for the procedure as I obtained it from elsewhere but it works fine.

USE [master]
GO
/****** Object: StoredProcedure [dbo].[stp_Admin_ReplicateUserLogins] Script Date: 10/29/2015 08:22:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[stp_Admin_ReplicateUserLogins] --'OriginalSourceDatabase', 1
 @PartnerServer sysname,
 @Debug bit = 0 -- 0 = Create Users, 1 = Display SQL command but doesn't execute query.
As
Declare @MaxID int,
 @CurrID int,
 @SQL nvarchar(max),
 @LoginName sysname,
 @IsDisabled int,
 @Type char(1),
 @SID varbinary(85),
 @SIDString nvarchar(100),
 @PasswordHash varbinary(256),
 @PasswordHashString nvarchar(300),
 @RoleName sysname,
 @Machine sysname,
 @PermState nvarchar(60),
 @PermName sysname,
 @Class tinyint,
 @MajorID int,
 @ErrNumber int,
 @ErrSeverity int,
 @ErrState int,
 @ErrProcedure sysname,
 @ErrLine int,
 @ErrMsg nvarchar(2048)
Declare @Logins Table (LoginID int identity(1, 1) not null primary key,
 [Name] sysname not null,
 [SID] varbinary(85) not null,
 IsDisabled int not null,
 [Type] char(1) not null,
 PasswordHash varbinary(256) null)
Declare @Roles Table (RoleID int identity(1, 1) not null primary key,
 RoleName sysname not null,
 LoginName sysname not null)
Declare @Perms Table (PermID int identity(1, 1) not null primary key,
 LoginName sysname not null,
 PermState nvarchar(60) not null,
 PermName sysname not null,
 Class tinyint not null,
 ClassDesc nvarchar(60) not null,
 MajorID int not null,
 SubLoginName sysname null,
 SubEndPointName sysname null)
Set NoCount On;
If CharIndex('\', @PartnerServer) > 0
 Begin
 Set @Machine = LEFT(@PartnerServer, CharIndex('\', @PartnerServer) - 1);
 End
Else
 Begin
 Set @Machine = @PartnerServer;
 End
-- Get all Windows logins from principal server
Set @SQL = 'Select P.name, P.sid, P.is_disabled, P.type, L.password_hash' + CHAR(10) +
 'From ' + QUOTENAME(@PartnerServer) + '.master.sys.server_principals P' + CHAR(10) +
 'Left Join ' + QUOTENAME(@PartnerServer) + '.master.sys.sql_logins L On L.principal_id = P.principal_id' + CHAR(10) +
 'Where P.type In (''U'', ''G'', ''S'')' + CHAR(10) +
 'And P.name <> ''sa''' + CHAR(10) +
 'And P.name Not Like ''##%''' + CHAR(10) +
 'and P.Name Not like ''NT SERVICE%''' + CHAR(10) +
 'And CharIndex(''' + @Machine + '\'', P.name) = 0;';
Insert Into @Logins (Name, SID, IsDisabled, Type, PasswordHash)
Exec sp_executesql @SQL;
-- Get all roles from principal server
Set @SQL = 'Select RoleP.name, LoginP.name' + CHAR(10) +
 'From ' + QUOTENAME(@PartnerServer) + '.master.sys.server_role_members RM' + CHAR(10) +
 'Inner Join ' + QUOTENAME(@PartnerServer) + '.master.sys.server_principals RoleP' +
 CHAR(10) + char(9) + 'On RoleP.principal_id = RM.role_principal_id' + CHAR(10) +
 'Inner Join ' + QUOTENAME(@PartnerServer) + '.master.sys.server_principals LoginP' +
 CHAR(10) + char(9) + 'On LoginP.principal_id = RM.member_principal_id' + CHAR(10) +
 'Where LoginP.type In (''U'', ''G'', ''S'')' + CHAR(10) +
 'And LoginP.name <> ''sa''' + CHAR(10) +
 'And LoginP.name Not Like ''##%''' + CHAR(10) +
 'And LoginP.name Not Like ''NT SERVICE%''' + CHAR(10) +
 'And RoleP.type = ''R''' + CHAR(10) +
 'And CharIndex(''' + @Machine + '\'', LoginP.name) = 0;';
Insert Into @Roles (RoleName, LoginName)
Exec sp_executesql @SQL;
-- Get all explicitly granted permissions
Set @SQL = 'Select P.name Collate database_default,' + CHAR(10) +
 ' SP.state_desc, SP.permission_name, SP.class, SP.class_desc, SP.major_id,' + CHAR(10) +
 ' SubP.name Collate database_default,' + CHAR(10) +
 ' SubEP.name Collate database_default' + CHAR(10) +
 'From ' + QUOTENAME(@PartnerServer) + '.master.sys.server_principals P' + CHAR(10) +
 'Inner Join ' + QUOTENAME(@PartnerServer) + '.master.sys.server_permissions SP' + CHAR(10) +
 CHAR(9) + 'On SP.grantee_principal_id = P.principal_id' + CHAR(10) +
 'Left Join ' + QUOTENAME(@PartnerServer) + '.master.sys.server_principals SubP' + CHAR(10) +
 CHAR(9) + 'On SubP.principal_id = SP.major_id And SP.class = 101' + CHAR(10) +
 'Left Join ' + QUOTENAME(@PartnerServer) + '.master.sys.endpoints SubEP' + CHAR(10) +
 CHAR(9) + 'On SubEP.endpoint_id = SP.major_id And SP.class = 105' + CHAR(10) +
 'Where P.type In (''U'', ''G'', ''S'')' + CHAR(10) +
 'And P.name <> ''sa''' + CHAR(10) +
 'And P.name Not Like ''##%''' + CHAR(10) +
 'And P.name Not Like ''NT SERVICE%''' + CHAR(10) +
 'And CharIndex(''' + @Machine + '\'', P.name) = 0;'
Insert Into @Perms (LoginName, PermState, PermName, Class, ClassDesc, MajorID, SubLoginName, SubEndPointName)
Exec sp_executesql @SQL;
Select @MaxID = Max(LoginID), @CurrID = 1
From @Logins;
While @CurrID <= @MaxID
 Begin
 Select @LoginName = Name,
 @IsDisabled = IsDisabled,
 @Type = [Type],
 @SID = [SID],
 @PasswordHash = PasswordHash
 From @Logins
 Where LoginID = @CurrID;
 If Not Exists (Select 1 From sys.server_principals
 Where name = @LoginName)
 Begin
 Set @SQL = 'Create Login ' + quotename(@LoginName)
 If @Type In ('U', 'G')
 Begin
 Set @SQL = @SQL + ' From Windows;'
 End
 Else
 Begin
 Set @PasswordHashString = '0x' +
 Cast('' As XML).value('xs:hexBinary(sql:variable("@PasswordHash"))', 'nvarchar(300)');
 Set @SQL = @SQL + ' With Password = ' + @PasswordHashString + ' HASHED, ';
 Set @SIDString = '0x' +
 Cast('' As XML).value('xs:hexBinary(sql:variable("@SID"))', 'nvarchar(100)');
 Set @SQL = @SQL + 'SID = ' + @SIDString + ';';
 End
 If @Debug = 0
 Begin
 Begin Try
 Exec sp_executesql @SQL;
 End Try
 Begin Catch
 Set @ErrNumber = ERROR_NUMBER();
 Set @ErrSeverity = ERROR_SEVERITY();
 Set @ErrState = ERROR_STATE();
 Set @ErrProcedure = ERROR_PROCEDURE();
 Set @ErrLine = ERROR_LINE();
 Set @ErrMsg = ERROR_MESSAGE();
 RaisError(@ErrMsg, 1, 1);
 End Catch
 End
 Else
 Begin
 Print @SQL;
 End
 If @IsDisabled = 1
 Begin
 Set @SQL = 'Alter Login ' + quotename(@LoginName) + ' Disable;'
 If @Debug = 0
 Begin
 Begin Try
 Exec sp_executesql @SQL;
 End Try
 Begin Catch
 Set @ErrNumber = ERROR_NUMBER();
 Set @ErrSeverity = ERROR_SEVERITY();
 Set @ErrState = ERROR_STATE();
 Set @ErrProcedure = ERROR_PROCEDURE();
 Set @ErrLine = ERROR_LINE();
 Set @ErrMsg = ERROR_MESSAGE();
 RaisError(@ErrMsg, 1, 1);
 End Catch
 End
 Else
 Begin
 Print @SQL;
 End
 End
 End
 Set @CurrID = @CurrID + 1;
 End
Select @MaxID = Max(RoleID), @CurrID = 1
From @Roles;
While @CurrID <= @MaxID
 Begin
 Select @LoginName = LoginName,
 @RoleName = RoleName
 From @Roles
 Where RoleID = @CurrID;
 If Not Exists (Select 1 From sys.server_role_members RM
 Inner Join sys.server_principals RoleP
 On RoleP.principal_id = RM.role_principal_id
 Inner Join sys.server_principals LoginP
 On LoginP.principal_id = RM.member_principal_id
 Where LoginP.type In ('U', 'G', 'S')
 And RoleP.type = 'R'
 And RoleP.name = @RoleName
 And LoginP.name = @LoginName)
 Begin
 If @Debug = 0
 Begin
 Exec sp_addsrvrolemember @rolename = @RoleName,
 @loginame = @LoginName;
 End
 Else
 Begin
 Print 'Exec sp_addsrvrolemember @rolename = ''' + @RoleName + ''',';
 Print ' @loginame = ''' + @LoginName + ''';';
 End
 End
 Set @CurrID = @CurrID + 1;
 End
Select @MaxID = Max(PermID), @CurrID = 1
From @Perms;
While @CurrID <= @MaxID
 Begin
 Select @PermState = PermState,
 @PermName = PermName,
 @Class = Class,
 @LoginName = LoginName,
 @MajorID = MajorID,
 @SQL = PermState + space(1) + PermName + SPACE(1) +
 Case Class When 101 Then 'On Login::' + QUOTENAME(SubLoginName)
 When 105 Then 'On ' + ClassDesc + '::' + QUOTENAME(SubEndPointName)
 Else '' End +
 ' To ' + QUOTENAME(LoginName) + ';'
 From @Perms
 Where PermID = @CurrID;
 If Not Exists (Select 1 From sys.server_principals P
 Inner Join sys.server_permissions SP On SP.grantee_principal_id = P.principal_id
 Where SP.state_desc = @PermState
 And SP.permission_name = @PermName
 And SP.class = @Class
 And P.name = @LoginName
 And SP.major_id = @MajorID)
 Begin
 If @Debug = 0
 Begin
 Begin Try
 Exec sp_executesql @SQL;
 End Try
 Begin Catch
 Set @ErrNumber = ERROR_NUMBER();
 Set @ErrSeverity = ERROR_SEVERITY();
 Set @ErrState = ERROR_STATE();
 Set @ErrProcedure = ERROR_PROCEDURE();
 Set @ErrLine = ERROR_LINE();
 Set @ErrMsg = ERROR_MESSAGE();
 RaisError(@ErrMsg, 1, 1);
 End Catch
 End
 Else
 Begin
 Print @SQL;
 End
 End
 Set @CurrID = @CurrID + 1;
 End
Set NoCount Off;

• user94939: "Run it from the Server that you have restored the database to and supply the original Server name as a parameter to the procedure." Thanks, and how does one "supply the original Server name as a parameter to the procedure" exactly?

Answer 5

You could always just try to re-link all users in the database to similarly-named logins on the database server.

ALTER 
 AUTHORIZATION
 ON 
 SCHEMA::db_owner
 TO 
 dbo
GO
DECLARE @username VARCHAR(64)
DECLARE @sql nvarchar(max)
DECLARE 
 UserCursor 
CURSOR FOR 
 SELECT 
 [name]
 FROM 
 sysusers
 WHERE 
 [name] NOT IN('dbo','guest','INFORMATION_SCHEMA','sys','public')
 AND 
 LEFT([name],3) <> 'db_' 
 AND 
 [name] NOT LIKE '%]%'
OPEN 
 UserCursor
FETCH NEXT 
FROM 
 UserCursor 
 INTO 
 @username
WHILE @@fetch_status <> -1
BEGIN
 SET @sql = 'ALTER USER [' + @username + '] WITH LOGIN=[' + @username + ']'
 PRINT @sql
 EXEC dbo.sp_executesql @sql;
 FETCH NEXT 
 FROM 
 UserCursor
 INTO 
 @username
END
CLOSE UserCursor
DEALLOCATE UserCursor

Answer 6

I thought it was worth noting this simple fix to posters problem. This is the script I run on SQL Server 2008 when I restore a production database from one server to development/test database on another server WHEN the Users name is in the Security> Users of the database BUT the 'login name' is missing from the users property on the General tab:

EXEC sp_change_users_login 'Auto_Fix','missingloginnamehere', NULL, 'passwordgoeshere';

MSDN reference here Note that the article recommends using ALTER USER instead for newer versions of SQL.