Detecting Orphaned Users

Posted on

Question :

We have exported Microsoft SQL 2014 server and rebuilt master DB. Users are missing now, so we need to detect orphaned users.

We have found article:

https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/troubleshoot-orphaned-users-sql-server?view=sql-server-ver15#:%7E:text=%20Identify%20orphaned%20users%20in%20those%20environments%20with,are%20user%20SID%27s%20in%20the%20user…%20More%20

It says to use code in user database:

SELECT dp.type_desc, dp.SID, 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';

What is this user database? Where the code should be run to detect orphaned users? Now the server logins are missing. How to get same users as in original database?

Answer :

That might’ve been a little bit of poor wording on Microsoft’s part but basically what they meant is run that query in the context of the database which you want to check if there are Orphaned Users for. So which ever database you run it under, it’ll tell you only for that database of there are Orphaned Users.

They clarify that slightly, just below the example query they provide in the article you linked:

The output lists the SQL Server authentication users and corresponding
security identifiers (SID) in the current database that are not linked
to any SQL Server login.

So if you have 3 databases on your server, for example, then you’d need to run that query 3 times (once in the context of each database) to verify for all of them.

SQl Server Logins vs. Database Users

The master database contains the SQL Server Logins which can be found in the [sys].[server_principles] management view.

A SQL Server Login is then mostly linked to a database user which is stored in the [database_name].[sys].[database_principles] view.

User Database

A user database is any database that is created by the user/dba and which does not belong to the SQL Server instances default/system database:

  • master
  • model
  • msdb
  • tempdb
  • RessourceDB (hidden)

Using the Supplied Script

In order to run the script you mentioned you would to this:

USE <database_name>
GO
SELECT dp.type_desc, dp.SID, 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';

In the USE <database_name> you would enter the name of your database that you want to check, e.g. USE AdminDB2. Or in the Script window you would change the focus from database pull-down window from master (default) to the required <database_name>.

Screen shot of database selector in SSMS

If you then run the script in the context of the user database then you should receive a list of orphaned users.

Leave a Reply

Your email address will not be published.