September 21, 2010 at 8:30 am
We have PRO and DEV servers that are running SQL Server 2000 SP4. Two servers have a database DB_CMS with lonin user MON under SQL Server Authentication. After I backup DB_CMS from RPO and restore it to DEV, the user MON can login to Query Analyzer, but it cannot see DB_CMS.
When I remap MON from Security - Login, I got error,
"This user is existing".
On Dev, MON cannot be deleted from DB_CMS because it is the object owner.
Any suggestion?
Thanks
September 21, 2010 at 8:55 am
Hello YTZ
The desynchronization between the user SID and the login SID happens after the restore because the SID are server specific.
Use this script to display desynchronized users
SELECT
sysusers.sid,
sysusers.name,
syslogins.sid,
syslogins.name,
*
FROM
sysusers
INNER JOIN
master..syslogins ON
syslogins.name COLLATE sql_latin1_general_cp1_ci_ai = sysusers.name COLLATE sql_latin1_general_cp1_ci_ai
WHERE
syslogins.sid <> sysusers.sid
Use this script to resynchronize
UPDATE
sysusers
SET
sid = sl.sid
FROM
sysusers su
INNER JOIN
master..syslogins sl ON
sl.name COLLATE sql_latin1_general_cp1_ci_ai = su.name COLLATE sql_latin1_general_cp1_ci_ai
WHERE
sl.sid <> su.sid
To enable Ad hoc updates to system catalogs
/*
SP_CONFIGURE 'ALLOW UPDATES',1
RECONFIGURE WITH OVERRIDE
*/
Don't forget to disable them after *only if you had to enable*
/*
SP_CONFIGURE 'ALLOW UPDATES',0
RECONFIGURE WITH OVERRIDE
*/
hth
September 21, 2010 at 9:24 am
sp_helprevlogin does it well. Have a look at this.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
September 21, 2010 at 12:58 pm
Or you can use the folowing
USE DB_NAME
GO
EXEC sp_change_users_login 'Report';
-- sp_change_users_login 'auto_fix','user_name'
sp_change_users_login 'update_one','user_name','Login_name'
Thank You,
Best regards,
SQLBuddy
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply