0

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?

1
  • Welcome to DBA.SE. Did you mean you exported the Microsoft SQL Server 2014 databaes and then rebuilt the SQL Server instance? Please add details to your question. if possible. (Hit edit). – John K. N. yesterday
0

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.

2
  • Actually I tried that on different databases, but it gives empty response. But users are missing in exported database, and they were there in original database. They should be orphaned. – Marin yesterday
  • @Marin So in other words you want to know which databases are missing Users that were in the original database before you exported it? – J.D. 23 hours ago
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.