How Can We Help?
When using the below script all databases will be updated. The output may not be very beautiful, but the effect is amazing.
DECLARE @DB_Name as varchar(100)
DECLARE database_cursor CURSOR FOR
SELECT name
FROM sys.databases where database_id>4
and name not like '%master%'
OPEN database_cursor
FETCH NEXT FROM database_cursor INTO @DB_Name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @DB_Name
DECLARE @sqluse varchar(100)
SET @sqluse = 'USE ' + @DB_Name
EXEC sp_sqlexec @sqluse
EXEC sp_change_users_login 'report'--See all orphaned users in the database.
DECLARE @OrphanedUsers TABLE
(
IndexKey Int IDENTITY(1,1) PRIMARY KEY,
UserName SysName,--nVarChar(128)
UserSID VarBinary(85)
)
INSERT INTO @OrphanedUsers
EXEC sp_change_users_login 'report'
DECLARE @CRLF as nVarChar
SET @CRLF = CHAR(10) + '&' + CHAR(13)--NOTE: Carriage-Return/Line-Feed will only appear in PRINT statements, not SELECT statements.
DECLARE @Sql as nVarChar(MAX)
SET @Sql = N''
DECLARE @IndexKey as Int
SET @IndexKey = 1
DECLARE @MaxIndexKey as Int
SET @MaxIndexKey = (SELECT COUNT(*) FROM @OrphanedUsers)
DECLARE @Count as Int
SET @Count = 0
DECLARE @UsersFixed as nVarChar(MAX)
SET @UsersFixed = N''
DECLARE @UserName as SysName--This is an orphaned Database user.
WHILE (@IndexKey <= @MaxIndexKey)
BEGIN
SET @UserName = (SELECT UserName FROM @OrphanedUsers WHERE IndexKey = @IndexKey)
IF 1 = (SELECT COUNT(*) FROM sys.server_principals WHERE Name = @UserName)--Look for a match in the Server Logins.
BEGIN
SET @Sql = @Sql + 'EXEC sp_change_users_login ''update_one'', [' + @UserName + '], [' + @UserName + ']' + @CRLF
SET @UsersFixed = @UsersFixed + @UserName + ', '
SET @Count = @Count + 1
END
SET @IndexKey = @IndexKey + 1
END
PRINT @Sql
EXEC sp_executesql @Sql
PRINT 'Total fixed: ' + CAST(@Count as VarChar) + '. Users Fixed: ' + @UsersFixed
SELECT ('Total fixed: ' + CAST(@Count as VarChar) + '. Users Fixed: ' + @UsersFixed)[Fixed]
EXEC sp_change_users_login 'report'--See all orphaned users still in the database.
FETCH NEXT FROM database_cursor INTO @DB_Name
END
CLOSE database_cursor
DEALLOCATE database_cursor