Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Fix All Orphaned Users Within Current Database (more info: https://www.linkedin.com/pulse/how-fix-orphaned-db-users-easily-eitan-blumin )
SET NOCOUNT ON;
DECLARE @db SYSNAME, @user NVARCHAR(MAX);
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp;
CREATE TABLE #tmp (DBName SYSNAME NULL, UserName NVARCHAR(MAX));
exec sp_MsforEachDB '
INSERT INTO #tmp
SELECT ''?'', dp.name AS user_name
FROM [?].sys.database_principals AS dp
LEFT JOIN sys.server_principals AS sp ON dp.SID = sp.SID
WHERE sp.SID IS NULL
AND authentication_type_desc = ''INSTANCE''
AND dp.name IN (SELECT name COLLATE database_default FROM sys.server_principals);'
DECLARE Orphans CURSOR FOR
SELECT DBName, UserName FROM #tmp;
OPEN Orphans
FETCH NEXT FROM Orphans INTO @db, @user
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @Command NVARCHAR(MAX)
SET @Command = N'USE ' + QUOTENAME(@db) + N'; ALTER USER ' + QUOTENAME(@user) + N' WITH LOGIN = ' + QUOTENAME(@user)
PRINT @Command;
EXEC (@Command);
FETCH NEXT FROM Orphans INTO @db, @user
END
CLOSE Orphans
DEALLOCATE Orphans
SET NOCOUNT ON;
DECLARE @user NVARCHAR(MAX);
DECLARE Orphans CURSOR FOR
SELECT dp.name AS user_name
FROM sys.database_principals AS dp
LEFT JOIN sys.server_principals AS sp ON dp.SID = sp.SID
WHERE sp.SID IS NULL
AND authentication_type_desc = 'INSTANCE'
AND dp.name IN (SELECT name FROM sys.server_principals);
OPEN Orphans
FETCH NEXT FROM Orphans INTO @user
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @Command NVARCHAR(MAX);
SET @Command = N'ALTER USER ' + QUOTENAME(@user) + N' WITH LOGIN = ' + QUOTENAME(@user)
PRINT @Command
EXEC (@Command);
FETCH NEXT FROM Orphans INTO @user
END
CLOSE Orphans
DEALLOCATE Orphans
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.