Reassociate all orphaned users to same-named logins in specified database

http://sqlity.net/en/2338/fix-orphaned-users/

/*

** Ctrl+Shift+M: replace parameters.

**

*/

 

SET NOCOUNT ON;

SET QUOTED_IDENTIFIER OFF;

GO

 

USE <database_name,,>;

GO

 

DECLARE @sql varchar(100);

 

DECLARE curSQL CURSOR FOR

SELECT “EXEC sp_change_users_login ‘UPDATE_ONE’, ‘” + name + “‘, ‘” + name + “‘”

FROM sysusers

WHERE issqluser = 1

AND suser_sname(sid) IS NULL

AND name NOT IN (‘dbo’, ‘guest’, ‘INFORMATION_SCHEMA’, ‘sys’ )

;

OPEN curSQL;

 

FETCH curSQL INTO @sql;

 

WHILE @@FETCH_STATUS = 0 BEGIN

EXEC (@sql);

PRINT @sql;

FETCH curSQL INTO @sql;

END

 

CLOSE curSQL;

DEALLOCATE curSQL;

GO

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.