script to dynamically fix ophaned users after db restore
- by JJgates
After performing a database restore, I want to run a dynamic script to fix ophaned users. My script below loops through all users that are displayed after executing sp_change_users_login 'report' and apply "alter user [username] with login = [username]" to fix SID conflicts verses static go statements. Although, I'm getting an "incorrect syntax error on line 15." can't figure out why...
DECLARE @Username varchar(100), @cmd varchar(100)
DECLARE userLogin_cursor CURSOR FAST_FORWARD
FOR
SELECT UserName = name FROM sysusers
WHERE issqluser = 1 and (sid IS NOT NULL AND sid <> 0×0)
AND suser_sname(sid) IS NULL
ORDER BY name
FOR READ ONLY
OPEN userLogin_cursor
FETCH NEXT FROM userLogin_cursor INTO @Username
WHILE @@fetch_status = 0
BEGIN
SET @cmd = ‘ALTER USER ‘+@username+‘ WITH LOGIN ‘+@username
EXECUTE(@cmd)
FETCH NEXT FROM userLogin_cursor INTO @Username
END
CLOSE userLogin_cursor
DEALLOCATE userLogin_cursor
