Synchronize the DB user SID with the Login SID

,

If you attach a DB on an SQL Server different from the SQL Server where the DB was initially created, you will usually come up with a situation where a login will be a DB user however, you cannot see this through the login properties screen. That happens because the name of the login exists in the database as a DB user but the sid in the DB is different from the sid of the login. This script will fix the problem in a "quick and dirty" fashion. Just change [ATTACHED-DB] with the name of the DB and 'DBUSER-TO-SYNC' with the problematic DB user/login and run it. Of course, the login and the DB user must exist...

/* Change the database name and the user name as desired */
Use master
GO

sp_configure @configname = 'allow updates', @configvalue = '1'
RECONFIGURE WITH OVERRIDE
GO

UPDATE u
SET u.sid = l.sid
FROM [ATTACHED-DB]..sysusers AS u
JOIN master..sysxlogins AS l ON u.[name] = l.[name] 
WHERE u.[name] = 'DBUSER-TO-SYNC'
GO

sp_configure @configname = 'allow updates', @configvalue = '0'
RECONFIGURE WITH OVERRIDE
GO

Rate

Share

Share

Rate