Skip to main content
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