TSQL: FixOrphanUsers.sql

FixOrphanUsers.sql

DECLARE @name varchar(max)
DECLARE @sql nvarchar(max)

DECLARE name CURSOR FOR
	select dp.name from sys.database_principals dp
	join sys.syslogins l on l.name = dp.name 
OPEN name

FETCH NEXT FROM name INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN

	SET @sql = 'ALTER USER [' + @name + '] WITH LOGIN =  [' + @name + ']'
--	SELECT @sql
	exec sp_executesql @sql

	FETCH NEXT FROM name INTO @name
END

CLOSE name
DEALLOCATE name