2 comments

  1. Here is an updated version of the instance-wide script, which takes into consideration the following scenarios:

    1. Orphan user is “dbo”, which means database has no owner -> Sets the owner to the sa login.
    2. Orphan user has a matching login -> Generates ALTER USER command to match the login.
    3. Orphan user has no matching login -> Generates DROP USER command, plus ALTER SCHEMA to move ownership of any owned schemas to dbo.

    SET NOCOUNT ON;
    DECLARE @db SYSNAME, @user NVARCHAR(MAX), @loginExists BIT, @saName SYSNAME, @ownedSchemas NVARCHAR(MAX);
    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 ‘
    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 = ”INSTANCE”;’

    IF EXISTS (SELECT NULL FROM #tmp)
    BEGIN
    DECLARE Orphans CURSOR FOR
    SELECT DBName, UserName, LoginExists, OwnedSchemas FROM #tmp;

    OPEN Orphans
    FETCH NEXT FROM Orphans INTO @db, @user, @loginExists, @ownedSchemas

    WHILE @@FETCH_STATUS = 0
    BEGIN
    DECLARE @Command NVARCHAR(MAX)

    IF @user = ‘dbo’
    SET @Command = N’USE ‘ + QUOTENAME(@db) + N’; ALTER AUTHORIZATION ON DATABASE::’ + QUOTENAME(@db) + N’ TO ‘ + QUOTENAME(@saName) + N’ — assign orphaned [dbo] to [sa]’
    ELSE IF @loginExists = 0
    SET @Command = N’USE ‘ + QUOTENAME(@db) + N’; ‘ + ISNULL(@ownedSchemas, N”) + N’ DROP USER ‘ + QUOTENAME(@user) + N’ — no existing login found’
    ELSE
    SET @Command = N’USE ‘ + QUOTENAME(@db) + N’; ALTER USER ‘ + QUOTENAME(@user) + N’ WITH LOGIN = ‘ + QUOTENAME(@user) + N’ — existing login found’

    PRINT @Command;
    –EXEC (@Command);

    FETCH NEXT FROM Orphans INTO @db, @user, @loginExists, @ownedSchemas
    END

    CLOSE Orphans
    DEALLOCATE Orphans
    END
    ELSE
    PRINT N’No orphan users found in instance!’

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.