Summary:
Option 1 - Use sp_change_users_login 'Update_One',
Option 2 - Backup & Restore Master
database,
Option 3 - Use Contained database
1.
Why Orphaned?
The database user either does not have a corresponding login, or
its SID does not match that for login. The SIDs for the database user and the
server login must be the same.
2. How
to find the SID?
SELECT * FROM sys.server_principals
SELECT * FROM sys.database_principals
3. How
to find the orphaned users in a database?
USE <database_name>;
GO;
sp_change_users_login @Action='Report';
GO;
4. How to resolve an orphaned user?
1)
Option 1 – manually mapping one at a time
by using sp_change_users_login
a)
The following command relinks the server
login account specified by <login_name> with
the database user specified by <database_user>.
USE <database_name>;
GO
sp_change_users_login @Action='update_one', @UserNamePattern='<database_user>',
@LoginName='<login_name>';
GO
--Example: Database user MB-Sales, which at first is mapped to
another login, is remapped to login MaryB.
EXEC sp_change_users_login 'Update_One', 'MB-Sales', 'MaryB';
b)
After you run the code in the preceding
step, the user can access the database. The user then can alter the password of
the <login_name> login account
by using the sp_password stored
procedure, as follows:
USE master
GO
sp_password @old=NULL, @new='password', @loginame='<login_name>';
GO
Note: Only logins
with the ALTER ANY LOGIN permission can change the password of another user's
login. However, only members of the sysadmin role can modify
passwords of sysadmin role members.
sp_password cannot
be used for Microsoft Windows accounts. Users connecting to an instance of SQL
Server through their Windows network account are authenticated by Windows;
therefore, their passwords can only be changed in Windows.
2)
Option 2 – backup and restore the master
database
3)
Option 3 – use contained database
