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

???? ?????? ???? ??????
???? ID: 274188 - ?? ???????? ?? ?????? ??? ?? ?? ???? ???? ???? ??.
??? ?? ??????? ???? | ??? ?? ??????? ????

?? ????? ??

?????

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 ???????? ??? ???? ????? ?? ??? ????? ???? ?????? ?? ????? ????:
240872INF: ???? ????? ?????? ????? ?? ?? ??????? ?? ??? ?? SQL ????? ?? ??? ?? ???? ???

???

???? ID: 274188 - ????? ???????: 02 ?????? 2010 - ??????: 2.0
???? ???? ???? ??:
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition
??????: 
kbcodesnippet kbpending kbprb kbmt KB274188 KbMthi
???? ?????? ????????
??????????: ?? ???? ?? ???? ??????? ?? ????? ?? Microsoft ????-?????? ?????????? ?????? ?????? ???? ??? ??. Microsoft ???? ??? ????-???????? ?? ????-???????? ????? ?????? ?? ???? ???????? ???? ?? ???? ????? ????? ??? ?? ??? ?????? ?? ???? ???? ???? ??? ????? ??. ???????, ????-???????? ???? ????? ???? ???? ???? ???. ?????, ????????, ?????-???? ?? ??????? ?? ???????? ?? ???? ???, ???? ?? ??? ?????? ???? ???? ??? ????? ??? ?? ???? ??. Microsoft ??????? ??? ???? ?? ?????? ?? ??????????, ????????? ?? ??? ?????? ?? ???? ????? ?? ???? ???????? ?? ??? ???? ????? ?? ??? ????????? ???? ??. Microsoft ????-?????? ?????????? ?? ????? ?????? ?? ?? ??? ??.
?????????? ?? ??????? ????????? ??????? ??:274188

??????????? ???

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com