How to Detect Orphaned Users in MS SQL Server

Hello everyone, it’s AlexIn Tech! Today, I will share a technical tip that I recently used during an SQL cluster migration. I needed to check for “orphaned users” after restoring my database on a new cluster. To do this, I wrote a small T-SQL script, and I will explain it in detail.

What is an “Orphaned User” in SQL Server?

In SQL Server, an “orphaned user” is a database user that is no longer associated with a valid login at the instance level. This often happens after restoring a database to a new environment.

Script to Detect “Orphaned Users”

Here is my simple and effective script.

-- Use your database
USE [YourDatabaseName];
GO

-- Create a temporary table to store user information
CREATE TABLE #DBUsers
(
    DBUserName NVARCHAR(128),
    DBUserSID VARBINARY(85)
);

-- Insert database user information into the temp table
INSERT INTO #DBUsers (DBUserName, DBUserSID)
SELECT name, sid 
FROM sys.database_principals 
WHERE type_desc IN ('SQL_USER', 'WINDOWS_USER', 'WINDOWS_GROUP');

-- Select and compare with instance logins
SELECT 
    u.DBUserName AS DatabaseUser, 
    u.DBUserSID AS DatabaseUserSID,
    p.name AS InstanceLogin,
    p.sid AS InstanceLoginSID,
    CASE 
        WHEN p.sid IS NULL THEN 'Orphaned User' 
        ELSE 'Matched' 
    END AS Status
FROM 
    #DBUsers u
LEFT JOIN 
    sys.server_principals p ON u.DBUserSID = p.sid;

-- Drop the temporary table
DROP TABLE #DBUsers;

Script Explanation

1. Selecting the Database: Replace [YourDatabaseName] with your database name.
2. Creating a Temporary Table: The script starts by creating a temporary table #DBUsers. In T-SQL, the # prefix indicates a temporary table that only exists for the session duration and is stored in tempdb (see the note at the end for more details).
3. Inserting Users: It then inserts database user names and SIDs (Security Identifiers) into this temporary table.
4. Comparing with Instance Logins: The main query compares the database user SIDs with instance-level logins to identify orphaned users.
5. Cleanup: Finally, the temporary table is dropped.

How to Use the Script?

1. Replace the Database Name: Start by replacing [YourDatabaseName] with your database name.
2. Run the Script: Execute the script in SQL Server Management Studio (SSMS).
3. Analyze the Results: Users marked as ‘Orphaned User’ are those you need to check.
4. Actions: You can choose to remove them or link them to new logins.

Understanding False Positives Among Orphaned Users

After reviewing how to use the script to detect orphaned users, it’s important to understand that sometimes, identifying an orphaned user doesn’t necessarily indicate a problem. In some cases, these orphaned users may be false positives. Here’s why:

1. Users Removed from Active Directory (AD): It’s common after restoring a database to find users who existed in AD when the database was created but have since been deleted from AD. These accounts, while still present in the database as users, are no longer in the instance logins because they didn’t exist in AD when the instance logins were restored. This generates “orphaned users” because they remain in the database users but not in the instance logins. Often, these are old accounts that were never deleted properly.
2. Database-Specific Users: Some databases have users created specifically for them, without a corresponding instance-level login. These users are often designed for roles or functions specific to the database and do not need an instance-level login.

I hope this guide helps you efficiently manage users in your SQL Server databases. Orphaned users can be a headache, but with the right tool, they are easy to spot and manage.

Enjoy 😎

AlexIn Tech

Note: Why Explicitly Drop a Temporary Table?

Reasons to Explicitly Drop a Temporary Table

1. Clarity and Best Practices: Including a DROP TABLE command for temporary tables is often a matter of clarity and best development practices. It clearly indicates that the table is no longer needed and that its lifecycle is proactively managed.
2. Resource Management: Although SQL Server automatically manages temporary table deletion, explicitly removing them once they are no longer needed can help free up resources like memory and disk space in tempdb faster, especially in long-running sessions or transactions.
3. Preventing Conflicts in Long Sessions: In particularly long or complex sessions with multiple operations, explicitly dropping a temporary table once it’s no longer needed can help prevent conflicts or confusion with other script parts that might use temporary tables with similar names.
4. Consistency and Portability: For developers working with multiple database management systems (DBMS), the habit of explicitly dropping all tables, including temporary ones, ensures consistency in resource management, regardless of the specific DBMS used.