January 5, 2010 at 3:34 am
Hi all - If I copy a db from SQL 2005 to another instance of SQL 2005 are the accounts matched automatically if they already exist in the target instance?
In SQL 2000 I could see when an account was orphaned but am unsure how to check this in SQL 2005.
Thanks
January 5, 2010 at 3:53 am
Use
sp_change_users_login 'REPORT' to find the orphan users..
Use the same procedure to remove orphan users.
January 5, 2010 at 3:59 am
You can check it with the procedure sp_change_users_login (you can read about it in BOL). If the needed logins don’t exist in the new database, you’ll need to create them in the new database. If you need to use the same passwords, but you don’t know the password, then you can use the script that is supplied by Microsoft in this KB article - http://support.microsoft.com/kb/918992/
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 5, 2010 at 4:43 am
ok great thanks
January 7, 2010 at 3:01 am
HI I have a follow up question to this. The sp_change_users proc does not work to find out windows level-principles that are orphaned. How can I find out which windows account have been orphaned other than by manualy going down the users list?
Thanks
January 7, 2010 at 1:59 pm
- It will not report orphaned users belonging to Windows accounts. (In this case, it is better to drop the user using sp_revokedbaccess, and readd the user using sp_grantdbaccess)
http://vyaskn.tripod.com/troubleshooting_orphan_users.htm
MJ
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply