Quick Scan Report – Orphan Database Users

What this check looks for

Database users whose SID does not match any login on the instance, excluding dbo. The check is skipped on Amazon RDS.

Why it matters

An orphaned user cannot log in, and the permissions granted to it are still sitting in the database waiting for something to claim them.

The immediate effect is an application that cannot connect, with an error about the login rather than about the user, which sends the investigation to the server level where the user is not.

The three ways it happens:

• A database was restored or attached from another instance. This is by far the most common. The user travels inside the database, the login lives on the server, and the login did not come with it. Even where a login with the same name exists on the new server, the SIDs differ, so the user is still orphaned.
• The login was dropped while the user was left behind.
• A login was dropped and recreated with the same name. This is the one that catches people out, because everything looks correct. The new login has a new SID, and the user still points at the old one.

The security angle is the reason this is filed under Security rather than Maintenance. An orphaned user is a set of permissions with no owner. Create a login with the right name and the wrong intent, map it, and it inherits whatever that user was granted. On a database restored from production into a test environment, those permissions are production permissions, and the test environment is usually the one with looser login control.

How to confirm it yourself

The supported report, run in the database concerned:

USE [YourDatabase];
GO
EXEC sp_change_users_login 'Report';

Or directly, which shows more:

USE [YourDatabase];
GO

SELECT dp.[name]        AS [database_user],
       dp.[type_desc],
       dp.[sid],
       dp.[create_date],
       sp.[name]        AS [matching_login]
  FROM sys.database_principals AS dp WITH (NOLOCK)
  LEFT JOIN sys.server_principals AS sp WITH (NOLOCK)
         ON sp.[sid] = dp.[sid]
 WHERE dp.[type] IN ('S', 'U', 'G')          -- SQL, Windows user, Windows group
   AND dp.[principal_id] > 4                 -- skip dbo, guest, INFORMATION_SCHEMA, sys
   AND dp.[authentication_type] <> 2         -- skip contained database users
   AND sp.[sid] IS NULL
 ORDER BY dp.[name];

Before fixing anything, find out what the user can do. That is the part worth knowing:

USE [YourDatabase];
GO

SELECT dp.[name] AS [database_user], r.[name] AS [role]
  FROM sys.database_role_members AS drm WITH (NOLOCK)
 INNER JOIN sys.database_principals AS dp WITH (NOLOCK) ON dp.[principal_id] = drm.[member_principal_id]
 INNER JOIN sys.database_principals AS r  WITH (NOLOCK) ON r.[principal_id]  = drm.[role_principal_id]
 ORDER BY dp.[name];

SELECT USER_NAME(p.[grantee_principal_id]) AS [database_user],
       p.[permission_name], p.[state_desc],
       OBJECT_NAME(p.[major_id])           AS [object_name]
  FROM sys.database_permissions AS p WITH (NOLOCK)
 WHERE p.[grantee_principal_id] > 4
 ORDER BY [database_user], p.[permission_name];

How to fix it

Decide per user: relink it, or remove it. Both are one statement, and the decision is the work.

To relink to an existing login of the same name, which is the modern syntax:

USE [YourDatabase];
GO
ALTER USER [Steve] WITH LOGIN = [Steve];

The older form still works and is what most examples show:

EXEC sp_change_users_login 'Update_One', 'Steve', 'Steve';

ALTER USER ... WITH LOGIN is preferred. sp_change_users_login is deprecated and does not work with Windows logins.

To remove a user nobody needs:

USE [YourDatabase];
GO
DROP USER [SomeoneWhoLeft];

A user that owns a schema cannot be dropped until the schema is transferred:

ALTER AUTHORIZATION ON SCHEMA::[TheirSchema] TO [dbo];

Prevent it for the next restore. When a login has to exist on several servers with the same SID, create it with the SID explicitly:

-- on the source
SELECT [name], [sid] FROM sys.sql_logins WHERE [name] = 'AppLogin';

-- on the target
CREATE LOGIN [AppLogin] WITH PASSWORD = 'the password', SID = 0x...;

That is the piece most restore runbooks are missing, and it turns a recurring problem into a one-off.

How long it takes

About three hours across an instance. Establishing what each user was for is most of it, and it is worth doing rather than mapping everything and hoping.

Related reports

Related checks

Frequently asked questions

Why is dbo excluded? dbo is always present and always maps to the database owner rather than to a login of its own. A dbo that does not resolve is the no-owner finding, which is a separate check.

We restored from production and everything is orphaned. That is the normal outcome. Scripting the logins with their SIDs from the source instance before restoring is what avoids it.

Should I just map every orphan to a login with the same name? Not without looking. A same-named login is not necessarily the same person or the same application, and mapping it hands over whatever permissions that user holds.

What about contained database users? They have no server login by design, which is the point of them, and they are excluded by the authentication_type filter above.