Fix Orphaned Users, Instance-Wide

2019-03-01 · by Hannah Vernon · in security, tools

The Overview

Orphaned users are database principals that are no longer associated with a server principal, or login. This happens frequently when restoring a database from one server onto another server that doesn’t have the same pre-existing logins as the original server. This post shows how to list instance-wide orphaned users on a per-database basis. FYI, database principals, or users, and server principals, or logins, are linked not by name, but by security identifier, or sid. So, it’s not enough to create logins on the new server with the same name, they need to be created using the WITH SID = ... syntax in the CREATE LOGIN statement, where the SID value is the same value as the login on the source server. This post shows you how to fix orphaned users, instance wide, for every database including system databases.

Maybe these guys are orphans, maybe they aren’t.

Identify the Little Buggers.

The code below shows orphaned users for every database in the instance, including system databases. It uses dynamic-SQL to fill the @cmd variable with a query for each online, non-contained database that looks for database users with no corresponding server login.

DECLARE @cmd nvarchar(max);
SET @cmd = N'';

SELECT @cmd = @cmd + CASE WHEN @cmd = N'' THEN N'' ELSE N'
UNION ALL
' END + N'
SELECT DatabaseName = ''' + d.name + N'''
    , UserName = dp.name
FROM ' + QUOTENAME(d.name) + N'.sys.database_principals dp
WHERE NOT EXISTS (
    SELECT 1
    FROM sys.server_principals sp
    WHERE sp.sid = dp.sid
    )
    AND dp.TYPE_DESC NOT IN ( --only users that should be mapped to a login
          ''APPLICATION_ROLE''
        , ''CERTIFICATE_MAPPED_USER''
        , ''EXTERNAL_USER''
        , ''ASYMMETRIC_KEY_MAPPED_USER''
        , ''DATABASE_ROLE''
        , ''EXTERNAL_GROUPS''
    )
    AND dp.name NOT IN ( --skip built-in principals
        ''sys''
        , ''guest''
        , ''INFORMATION_SCHEMA''
    )
'
FROM sys.databases d
WHERE d.state_desc = N'ONLINE' --only inspect databases that are online
    AND d.containment_desc = N'NONE' --skip contained databases
ORDER BY d.name;
PRINT @cmd;
EXEC sys.sp_executesql @cmd;

DECLARE @cmd nvarchar(max);

SET @cmd = N'';

SELECT @cmd = @cmd + CASE WHEN @cmd = N'' THEN N'' ELSE N'

UNION ALL

' END + N'

SELECT DatabaseName = ''' + d.name + N'''

, UserName = dp.name

FROM ' + QUOTENAME(d.name) + N'.sys.database_principals dp

WHERE NOT EXISTS (

SELECT 1

FROM sys.server_principals sp

WHERE sp.sid = dp.sid

)

AND dp.TYPE_DESC NOT IN ( --only users that should be mapped to a login

''APPLICATION_ROLE''

, ''CERTIFICATE_MAPPED_USER''

, ''EXTERNAL_USER''

, ''ASYMMETRIC_KEY_MAPPED_USER''

, ''DATABASE_ROLE''

, ''EXTERNAL_GROUPS''

)

AND dp.name NOT IN ( --skip built-in principals

''sys''

, ''guest''

, ''INFORMATION_SCHEMA''

)

FROM sys.databases d

WHERE d.state_desc = N'ONLINE' --only inspect databases that are online

AND d.containment_desc = N'NONE' --skip contained databases

ORDER BY d.name;

PRINT @cmd;

EXEC sys.sp_executesql @cmd;

Microsoft Docs has a page dedicated to detecting and resolving orphaned users. However, when you have a large number of databases with this problem, the code above is helpful at identifying orphaned users across all databases at once.

Ok, Let’s Fix ‘Em!

The next piece of code helps fix orphaned users by reconnecting them to logins that have precisely the same name, but a differing SID. This code is a variant of the above code that dynamically creates ALTER USER statements. A statement is created for each orphaned user where there is a match-by-name in the list of server logins. Once the list of dynamically created ALTER USER statements are compiled, the commands to fix orphaned users are automatically executed.

DECLARE @cmd nvarchar(max);
SET @cmd = N'';
DECLARE @ServerCollation sysname = (SELECT d.collation_name FROM sys.databases d WHERE d.name = 'master');

SELECT @cmd = @cmd + CASE WHEN @cmd = N'' THEN N'' ELSE N'
UNION ALL
' END + N'
SELECT N''USE ' + QUOTENAME(d.name) + N';ALTER USER '' + QUOTENAME(dp.name) COLLATE ' + @ServerCollation + N' + N'' WITH LOGIN = '' + QUOTENAME(sp.name) COLLATE ' + @ServerCollation + N' + N'';''
FROM ' + QUOTENAME(d.name) + N'.sys.database_principals dp
    INNER JOIN sys.server_principals sp ON sp.sid <> dp.sid
        AND sp.name = dp.name COLLATE ' + @ServerCollation + N'
WHERE sp.type_desc = ''SQL_LOGIN''
    AND NOT EXISTS (
        SELECT 1
        FROM sys.server_principals sp
        WHERE sp.sid = dp.sid
    )
    AND dp.TYPE_DESC NOT IN ( --only users that should be mapped to a login
          ''APPLICATION_ROLE''
        , ''CERTIFICATE_MAPPED_USER''
        , ''EXTERNAL_USER''
        , ''ASYMMETRIC_KEY_MAPPED_USER''
        , ''DATABASE_ROLE''
        , ''EXTERNAL_GROUPS''
    )
    AND dp.name NOT IN ( --skip built-in principals
        ''sys''
        , ''guest''
        , ''INFORMATION_SCHEMA''
    )
'
FROM sys.databases d
WHERE d.state_desc = N'ONLINE' --only inspect databases that are online
    AND d.containment_desc = N'NONE' --skip contained databases
ORDER BY d.name;

IF OBJECT_ID(N'tempdb..#t', N'U') IS NOT NULL
DROP TABLE #t;
CREATE TABLE #t 
(
    cmd nvarchar(max) NOT NULL
);
INSERT INTO #t (cmd)
EXEC sys.sp_executesql @cmd;
SET @cmd = N'';
SELECT @cmd = @cmd + #t.cmd
FROM #t;
PRINT @cmd;
EXEC sys.sp_executesql @cmd;

DECLARE @cmd nvarchar(max);

SET @cmd = N'';

DECLARE @ServerCollation sysname = (SELECT d.collation_name FROM sys.databases d WHERE d.name = 'master');

SELECT @cmd = @cmd + CASE WHEN @cmd = N'' THEN N'' ELSE N'

UNION ALL

' END + N'

SELECT N''USE ' + QUOTENAME(d.name) + N';ALTER USER '' + QUOTENAME(dp.name) COLLATE ' + @ServerCollation + N' + N'' WITH LOGIN = '' + QUOTENAME(sp.name) COLLATE ' + @ServerCollation + N' + N'';''

FROM ' + QUOTENAME(d.name) + N'.sys.database_principals dp

INNER JOIN sys.server_principals sp ON sp.sid <> dp.sid

AND sp.name = dp.name COLLATE ' + @ServerCollation + N'

WHERE sp.type_desc = ''SQL_LOGIN''

AND NOT EXISTS (

SELECT 1

FROM sys.server_principals sp

WHERE sp.sid = dp.sid

)

AND dp.TYPE_DESC NOT IN ( --only users that should be mapped to a login

''APPLICATION_ROLE''

, ''CERTIFICATE_MAPPED_USER''

, ''EXTERNAL_USER''

, ''ASYMMETRIC_KEY_MAPPED_USER''

, ''DATABASE_ROLE''

, ''EXTERNAL_GROUPS''

)

AND dp.name NOT IN ( --skip built-in principals

''sys''

, ''guest''

, ''INFORMATION_SCHEMA''

)

FROM sys.databases d

WHERE d.state_desc = N'ONLINE' --only inspect databases that are online

AND d.containment_desc = N'NONE' --skip contained databases

ORDER BY d.name;

IF OBJECT_ID(N'tempdb..#t', N'U') IS NOT NULL

DROP TABLE #t;

CREATE TABLE #t

(

cmd nvarchar(max) NOT NULL

);

INSERT INTO #t (cmd)

EXEC sys.sp_executesql @cmd;

SET @cmd = N'';

SELECT @cmd = @cmd + #t.cmd

FROM #t;

PRINT @cmd;

EXEC sys.sp_executesql @cmd;

Let me know if you found this script useful, or if you find some edge-case that I haven’t covered.

This post is part of our series on security

Fix Orphaned Users, Instance-Wide

The Overview

Identify the Little Buggers.

Ok, Let’s Fix ‘Em!

Related

One Response

Leave a ReplyCancel reply

Search

Categories

Pages

Meta