script to dynamically fix ophaned users after db restore

Question

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 applys an "alter user [username] with login = [username]" statement to fix SID conflicts. I'm getting an "incorrect syntax error on line 15" and can't figure out why...help..

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

Answer 1

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 <> 0x01)
     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 

Answer 2

Orphaned users can be fixed by using the [dbo].[sp_change_users_login] stored procedure.

Loop through all your users and execute the procedure

Good Luck

DECLARE @UserCount INT
DECLARE @UserCurr INT
DECLARE @userName VARCHAR(100)
DECLARE @vsql NVARCHAR(4000)
DECLARE @Users TABLE(
id INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
userName VARCHAR(100))
INSERT INTO @Users(UserName) 
SELECT [name] FROM 
--
master.[dbo].sysUsers -- SQL 2008 & SQL 2005
--master.dbo.sysxlogins -- SQL 2000


SELECT @UserCount = max([id]) FROM @Users
SET @UserCurr = 1

WHILE (@UserCurr <= @UserCount)
BEGIN
 SELECT @userName=userName FROM @Users WHERE [id] =@UserCurr
 SET @vsql = '[dbo].[sp_change_users_login] ''AUTO_FIX'',''' + @userName + ''''
 -- EXEC(@vsql)
 PRINT @vsql
 SET @UserCurr = @UserCurr + 1
END