September 24, 2002 at 1:58 am
I have recovered a database rion from a backup of a database rion a production database on another server. I forced the recovery over the existing database. Everything worked fine except that the users for this database did not appear. I therefore decided to script the users from the source database and run this on the target database . It seems to have done this without errors but I still cannot visualise the users in the users hireachy of the Enterprice manager console for this database. I then decided to create one or two of the users manually , this complained that these users were already present in the database. These users I noticed in the logons. Even when I remove this user from the logon on trying to recreate this logon, it complain that this user or role exists in the database. Please help. What is happening here? An urgent reply would be appreciated.
September 24, 2002 at 4:41 am
Sounds like the users are orphaned. Try running sp_change_users_login, see BOL or there are quite a few articles here on the site that provide details. What happens is that the rowid for the userid no longer matches to a login, this proc "fixes" that by updating sysusers.
Andy
September 24, 2002 at 7:50 am
Andy's right on with how to identify orphan users.
When you scripted users did you retain the SID's, if not your sysusers will not align with syslogins. Although as Andy stated using the sp_change_users_login, you can map a sysuser to a syslogin record.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 24, 2002 at 8:24 am
Thanks alot Andy and Greg. I did use EXEC sp_change_users_login ‘report’ to identify the orphaned users and
EXEC sp_change_users_login ‘Update_One’, ‘<db_user_name>’, ‘<sql_server_login_name>’ to put things right. I got the step by step instudctions from an application consultant. I would like you both to point me directly to the examples you have in your website as I have tried looking but could not find them.
Cheers
Grace
September 24, 2002 at 8:24 am
Thanks alot Andy and Greg. I did use EXEC sp_change_users_login ‘report’ to identify the orphaned users and
EXEC sp_change_users_login ‘Update_One’, ‘<db_user_name>’, ‘<sql_server_login_name>’ to put things right. I got the step by step instudctions from an application consultant. I would like you both to point me directly to the examples you have in your website as I have tried looking but could not find them.
Cheers
Grace
September 24, 2002 at 8:53 am
I don't have an example of how to script logins with sids on my website. But I found the script here:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q246133
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply