SYMPTOMS When you restore a database backup to another server, you may experience a problem with orphaned users. The Troubleshooting Orphaned Users' topic in SQL Server Books Online does not outline the exact steps for troubleshooting this problem.
Troubleshooting Orphaned Users
When you restore a database backup to another server, you may experience a problem with orphaned users. The following scenario illustrates the problem and shows how to resolve it.
A. Showing a report of the current user to login mappings
The following example produces a report of the users in the current database and their security identifiers (SIDs).
Copy
EXEC sp_change_users_login 'Report';
B. Mapping a database user to a new SQL Server login
In the following example, a database user is associated with a new SQL Server login. Database user MB-Sales, which at first is mapped to another login, is remapped
USE AdventureWorks2012;
GO
EXEC sp_change_users_login 'Update_One', 'MB-Sales', 'MaryB';
GO
Use Northwind
go
sp_change_users_login 'update_one', (restore users id)'test',(current database user id) 'test'
sp_change_users_login :
@Action
@UserNamePattern
@LoginName
@Password
Subscribe to:
Post Comments (Atom)
1 comment:
http://www.fileformat.info/tip/microsoft/sql_orphan_user.htm
First, make sure that this is the problem. This will lists the orphaned users:
EXEC sp_change_users_login 'Report'
If you already have a login id and password for this user, fix it by doing:
EXEC sp_change_users_login 'Auto_Fix', 'user'
If you want to create a new login id and password for this user, fix it by doing:
EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'
Post a Comment