PRB: "Troubleshooting Orphaned Users" Topic in Books Online is Incomplete

When you restore a database backup to another server, you may experience a problem with orphaned users. TheTroubleshooting Orphaned Users'topic in SQL Server Books Online does not outline the exact steps for troubleshooting this problem.

This article describes how you can troubleshoot orphaned users.

Microsoft ?? ?????? ?? ?? ?? ?? ???? ?? ???? ??? ???????? Microsoft ???????? ??? ?? ?? ?????? ??..

Although the terms login and user are often used interchangeably, they are very different. ?????????? ??????? ?? ??? ???? ???-?? ?? ????? ???? ???? ??, ?? ??????? ??? ?????????? ???? ??????? ????? ?? ????????? ??????? ?? ??? ????? ???? ???? ??? ????? ?????? ???????????? ?? ??? ??????? ?????????? (SID) ?? ??? ???? ??? ???-?? SQL ????? ????? ?? ????? ?? ??? ?????? ??? ???????? ???? ?? ??? ????? ????? ????? ?? ?? ????????? ?? "???????" ??? ???? ??? ?? ????? ???? ?? ??? ??? SQL Server ??????? ?????????? ??????? ???? ?????? ??? ??????? ??? ?? ?????????? ?? ????????? ???? ?? ??? ?? ?????????? ???? ?? ????? ????? ??? ??? ?????????? ???? ??? ??????? ????? ???? ?? ??? ?? ??????? ??? ????? ??, ?? ???-?? ?? ????? ?? ??? ?? ?? ?????????? ???? ????? ??????? ??? ?? ?????????? SQL Server ????? ?? ?????? ???? ??? ????? ?? ???? ??? ?? ?????? ?? ??? ?? ????? ?? ?? ??????? "?????" ?????????? ???? ??? ???? ???? ??? ??????? ?????????? ???? ???? ?? ?? ??? ?? ???? ?? ??? ????? ?????????? ????? ??? Conversely, ??? ?????????? ??????? ??? ????? ??, ????? ??? ????? ?? ??????? ??, ?? ?????????? ???? ?? SQL Server ????? ??? ??? ?? ???? ??? ??????

???? ??????? ???????????? ???? ???? ?? ?? ???? ????? ????? ?? ??? ????? ???????????? ?? ????????? ?? ?? ??? ????? ???? ?? ??????? ????? ???? ?? ?? ????? ??? ???????????? ?? ??? ?????? ? ??? ?? ?????? ???? "orphaned ??????????." ?? ??? ??? ???? ???? ??

Orphaned ?????????? ?????? ??????

?? ?? ???? ????? ?? ??? ??????? ?? ????? ???????????? ????, ?? orphaned ???????????? ?? ??? ??? ?????? ????? ?? ????? ???????? ?????? illustrates ?? ?? ??? ?? ???? ?? ??? ???? ?????? ???

1. ????? ???? ????? ??????? ??? ??????, ?? ??????? ??????? ????????? ?? ??? ??? ????????? ????:

   Use master go sp_addlogin 'test', 'password', 'Northwind'
   						

2. ?? ??? ???? ?? ?????????? ?? ??? ????? ???? ????:

   Use Northwind go sp_grantdbaccess 'test'
   						

3. ??????? ?? ??????

   BACKUP DATABASE Northwind
   TO DISK = 'C:\MSSQL\BACKUP\Northwind.bak'
   						

4. SQL ????? ???? ????? ????? ?? ??? ??????? ???????????? ????:

   RESTORE DATABASE Northwind
   FROM DISK = 'C:\MSSQL\BACKUP\Northwind.bak'
   						

   ???????????? ??????? ??? ???? ?????????? ?? "??? ???? ?????, ?? ??? ??????"????"orphaned ???? ????" ??????? ???? ?? ??? ???
5. ??, orphaned ???????????? ?? ?????, ????? ?? ???:

   Use Northwind go sp_change_users_login 'report'
   						

   ?????? ?? ??? ?????, ???????????? ?? ??? ??? ??? ????? ?? ?? ???? ???sysusers?????? ????, ????????? ???????, ??sysxlogins?????? ???? ????? ??????? ??? ???

Orphaned ?????????? ?? ????? ?? ??? ???

1. ????? ??? ?? orphaned ?????????? ?? ??? ????? ??????? ?? ?????:

   Use Northwind
   go
   sp_change_users_login 'update_one', 'test', 'test'
   						

   ?? ????? relinks?????"test" with the the Northwind databaseuser"test". Thesp_change_users_loginstored procedure can also perform an update of all orphaned users with the "auto_fix" parameter but this is not recommended because SQL Server attempts to match logins and users by name. For most cases this works; however, if the wrong login is associated with a user, a user may have incorrect permissions.
2. After you run the code in the preceding step, the user can access the database. ?????????? ?? ???????? ?? ??? ??????? ?? ?? ???? ??sp_password???????? ?????????:

   Use master 
   go
   sp_password NULL, 'ok', 'test'
   						

   Microsoft Windows NT ??????? ???? ?? ??? ?? ???????? ????????? ???? ???? ?? ???? ??? ???????????? ?? ???? Windows NT ??????? ???? ?? ?????? ?? ?? SQL Server ????? ?? ?????? ???? ?? ??? Windows NT ?????? ?????????? ???; ?????, ???? ??????? ???? ????????? ???? ?? ???? Windows NT ??? ???
   
   ???? sysadmin ??? ?? ??????? ?? ???? ???? ?????????? ?? ????? ???? ?? ??? ??????? ????????? ?? ?????

??????? ????? ?? ??? ?? ???? ??? ?? ?? ?????? ???????? ?? ?? ???? ?? ??? ???? ???? ?? ???? ??? ???????? ??????? ?? ??? Microsoft ???????? ??? ???? ????? ?? ??? ????? ???? ?????? ?? ????? ????: