script to dynamically fix ophaned users after db restore

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 &开发者_开发技巧lt;> 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

---

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

---

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 

---

I've used a similar approach, wrapping the code in a stored procedure:

USE [master]
GO

CREATE PROCEDURE [sp_AutoFixAllUsers]
AS
BEGIN

    DECLARE @AutoFixCommand NVARCHAR(MAX)
    SET @AutoFixCommand = ''

    SELECT --dp.[name], dp.[sid] AS [DatabaseSID], sp.[sid] AS [ServerSID],
        @AutoFixCommand = @AutoFixCommand + ' '
         + 'EXEC sp_change_users_login ''Auto_Fix'', ''' + dp.[name] + ''';'-- AS [AutoFixCommand]
    FROM sys.database_principals dp
    INNER JOIN sys.server_principals sp
        ON dp.[name] = sp.[name] COLLATE DATABASE_DEFAULT
    WHERE dp.[type_desc] IN ('SQL_USER', 'WINDOWS_USER', 'WINDOWS_GROUP')
    AND sp.[type_desc] IN ('SQL_LOGIN', 'WINDOWS_LOGIN', 'WINDOWS_GROUP')
    AND dp.[sid] <> sp.[sid]

    IF (@AutoFixCommand <> '')
    BEGIN
        PRINT 'Fixing users in database: ' + DB_NAME()
        PRINT @AutoFixCommand
        EXEC(@AutoFixCommand)
        PRINT ''
    END
END
GO

I then used the sys.sp_MS_marksystemobject stored procedure to make my stored procedure available in all user databases (allowing it to operate on local objects)

EXEC sys.sp_MS_marksystemobject 'sp_AutoFixAllUsers'

You can then run it as follows:

EXEC [MyDB].[dbo].[sp_AutoFixAllUsers]

Or for every database using sp_msforeachdb:

EXEC sp_msforeachdb '[?].[dbo].[sp_AutoFixAllUsers]'