April 18, 2005 at 11:09 am
Recently an new script was submitted titled: 'Synchronize the DB user SID with the Login SID'
This script is to be used to sync userid's with logins. The script is as follows:
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
This manipulates the system tables. I was under the impression that this is not a good practice. Also, I believe there is a system SP that takes care of this.
The SP I am think is: 'sp_Change_Users_Login'. The use would be as follows:
sp_Change_Users_Login 'Report'
This will list all the database User Id's that are out of sync.
To sync the User Id's up use:
sp_Change_Users_Login 'Auto_Fix', '{User Id}'
I believe it is wiser to use the System SP to sync the Id's instead of directly manipulating the system tables. If I am wrong on my thinking here, please let me know.
April 19, 2005 at 3:19 am
I concur with you that updating system tables should be used only as a last resort. This is not such a case. Instead of 'auto_fix' option I use the following script:
use specify_db
DECLARE @UserName nvarchar(255)
DECLARE orphanuser_cur cursor for
SELECT UserName = name
FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0x0) and suser_sname(sid) is null
ORDER BY name
--cannot translate sid to existing user=orphaned
OPEN orphanuser_cur
FETCH NEXT FROM orphanuser_cur INTO @UserName
WHILE (@@fetch_status = 0)
BEGIN
PRINT @UserName + ' user name being resynced'
EXEC sp_change_users_login 'Update_one', @UserName, @UserName
FETCH NEXT FROM orphanuser_cur INTO @UserName
END
CLOSE orphanuser_cur
DEALLOCATE orphanuser_cur
go
In sensitive security situation you could just output sp_change_users_login commands for every user and check them visually before applying in order not to introduce unwanted changes to your db.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply