Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Fix all orphaned users in current database, or all databases in the instance (more info: https://eitanblumin.com/2018/10/31/t-sql-script-to-fix-orphaned-db-users-easily/)
/*
Author: Eitan Blumin | https://eitanblumin.com
Date Created: 2018-01-02
Last Update: 2020-03-30
Description:
Fix All Orphaned Users Within Current Database, or all databases in the instance.
Handles 3 possible use-cases:
1. Login with same name as user exists - generate ALTER LOGIN to map the user to the login.
2. No login with same name exists - generate DROP USER to delete the orphan user.
3. Orphan user is [dbo] - change the database owner to SA (or whatever SA was renamed to)
More info: https://eitanblumin.com/2018/10/31/t-sql-script-to-fix-orphaned-db-users-easily/
*/
DECLARE
@Database SYSNAME = NULL -- Filter by a specific database. Leave NULL for all databases.
SET NOCOUNT ON;
-- Variable declaration
DECLARE @user NVARCHAR(MAX), @loginExists BIT, @saName SYSNAME, @ownedSchemas NVARCHAR(MAX);
-- Find the actual name of the "sa" login
SELECT @saName = [name] FROM sys.server_principals WHERE sid = 0x01;
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp;
CREATE TABLE #tmp (DBName SYSNAME NULL, UserName NVARCHAR(MAX), LoginExists BIT, OwnedSchemas NVARCHAR(MAX));
exec sp_MsforEachDB 'IF DATABASEPROPERTYEX(''?'', ''Status'') = ''ONLINE'' AND DATABASEPROPERTYEX(''?'', ''Updateability'') = ''READ_WRITE''
INSERT INTO #tmp
SELECT ''?'', dp.name AS user_name
, CASE WHEN dp.name IN (SELECT name COLLATE database_default FROM sys.server_principals) THEN 1 ELSE 0 END AS LoginExists
, OwnedSchemas = (
SELECT ''ALTER AUTHORIZATION ON SCHEMA::'' + QUOTENAME(sch.name) + N'' TO [dbo]; ''
FROM [?].sys.schemas AS sch
WHERE sch.principal_id = dp.principal_id
FOR XML PATH ('''')
)
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 IN (''INSTANCE'',''WINDOWS'')
AND DATABASEPROPERTYEX(''?'',''Updateability'') = ''READ_WRITE'';'
IF EXISTS (SELECT NULL FROM #tmp WHERE DBName = @Database OR @Database IS NULL)
BEGIN
DECLARE Orphans CURSOR FOR
SELECT DBName, UserName, LoginExists, OwnedSchemas
FROM #tmp
WHERE DBName = @Database OR @Database IS NULL;
OPEN Orphans
FETCH NEXT FROM Orphans INTO @Database, @user, @loginExists, @ownedSchemas
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @Command NVARCHAR(MAX)
IF @user = 'dbo'
SET @Command = N'USE ' + QUOTENAME(@Database) + N'; ALTER AUTHORIZATION ON DATABASE::' + QUOTENAME(@Database) + N' TO ' + QUOTENAME(@saName) + N' -- assign orphaned [dbo] to [sa]'
ELSE IF @loginExists = 0
SET @Command = N'USE ' + QUOTENAME(@Database) + N'; ' + ISNULL(@ownedSchemas, N'') + N' DROP USER ' + QUOTENAME(@user) + N' -- no existing login found'
ELSE
SET @Command = N'USE ' + QUOTENAME(@Database) + N'; ALTER USER ' + QUOTENAME(@user) + N' WITH LOGIN = ' + QUOTENAME(@user) + N' -- existing login found'
PRINT @Command;
--EXEC (@Command);
FETCH NEXT FROM Orphans INTO @Database, @user, @loginExists, @ownedSchemas
END
CLOSE Orphans
DEALLOCATE Orphans
END
ELSE
PRINT N'No orphan users found!'
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.