script to synchronize sid for login and users
-
I would schedule an automatic restore of a database.
I have that part of script done. But sometimes after restore the users are out of sync with logins,
Is there any script that sync the ID of logins and users?
I need to automate this as part of retore database job too.
Thanks
-
Before you restore the DB, make sure that the Logins are created with the right SPID. That is a one time shot. When you restore the back up, the Login will be associated with the user in the DB with the right SID.
-Roy
-
If you are restoring to a different server - remember logins are held in Master, so if your not restoring Master as well, those logins will not exist.
-
You do not need to restore master. That is the beauty of it. Create a script create logins with the same SID. Like shown below.
CREATE LOGIN TestUser WITH PASSWORD = 'dsjhlkdjklsdjklsjfdljfdlfjdslsdklsjd', CHECK_POLICY = ON, SID =
0xQW7443KDSLKJI8JKJL490U89KJ488484
Once you have this, you do not need to restore the Master and when the DB is restored, the user will get linked to the right login.
-Roy
-
This what we use to fix all SQL Server logins in a restored database, wrapped in a stored procedure. Run it in the restored database.
SET NOCOUNT ON
DECLARE CUSR CURSOR LOCAL STATIC FOR
SELECT u.name
FROM sysusers u
JOIN master.dbo.syslogins l ON l.name=u.name
WHERE u.sid!=l.sid AND u.issqluser=1
ORDER BY u.name
OPEN CUSR
DECLARE @user SYSNAME
WHILE 1=1 BEGIN
FETCH NEXT FROM CUSR INTO @user
IF @@FETCH_STATUS!=0 BREAK
PRINT CHAR(13)+'*** Fixing User '+@user+' ...'
IF @debug=0 EXEC sp_change_users_login 'auto_fix', @user
END
CLOSE CUSR DEALLOCATE CUSR
-
I bulk copy the syslogins table to a file and transfer to my DR server. On the dr box I import to a temp table and where the user in my temp table doesn't exist on the dr box I create it retaining all passwords and SIDS.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs"
-
Refer http://sqlism.blogspot.com/2014/09/script-to-sync-up-logins-and-database.html
syncup the logins and users
USE DATABASE
GO
EXEC sp_change_users_login 'update_one'
,'databse username'
,'loginname'
GO
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply