Hello everyone, it's AlexIn Tech! Today, I'm going to share with you a technical tip I recently used during the migration of a SQL cluster. I needed to check for the existence of "orphaned users" after restoring my database to a new cluster. For this purpose, I wrote a small TSQL script and I'm going to explain it to you in detail.
What is an "Orphaned User" in SQL Server?
In SQL Server, an "orphaned user" is a database user who is no longer associated with a valid connection at the instance level. This often happens after restoring a database to a new environment.
The Script to Detect "Orphaned Users"
Here's my little script, it works great.
-- 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;
Explanation of the Script
Database Selection: Replace [YourDatabaseName] with the name of your database to target it.
Creating a Temporary Table: The script starts by creating a temporary table #DBUsers. In TSQL, the # prefix indicates a temporary table that exists only for the duration of the session and is stored in tempdb.*(see the note at the end of the article for more information)
Inserting Users: Next, it inserts the names and SIDs (Security Identifier) of the database users into this temporary table.
Comparison with Instance Logins: The main query compares the SIDs of the database users with those of the instance logins to identify the "orphaned users".
Cleanup: Finally, the temporary table is deleted.
How to Use the Script?
Replace the Database Name: Start by replacing [YourDatabaseName] with the name of your database.
Execute the Script: Run the script in your SQL Server Management Studio (SSMS) environment.
Analyze the Results: Users marked as 'Orphaned User' are those you need to examine.
Actions: You can then choose to delete them or link them to new connections.
Understanding False Positives Among "Orphaned Users"
After explaining how to use the script to detect "orphaned users", it's important to understand that sometimes identifying an "orphaned user" does not necessarily signal a problem. In some cases, these orphaned users can be false positives. Here's why:
Users Deleted from Active Directory (AD): It is common after a DB restore to find users who existed in the AD when the database was created but have since been removed from the AD. These accounts, while still present in the database as users, are no longer in the instance logins because when the instance logins were restored they no longer existed in the AD. This generates "orphaned users" because they are in the DB users but not in the instance logins. Often, these are relics that had not been removed.
Database-Specific Users: Some databases have users created specifically for them, with no correspondence at the instance level. These users are often designed for roles or functions specific to the database and do not need a connection at the instance level.
I hope this guide helps you effectively 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
*Nota Bene: Why Explicitly Delete a Temporary Table?
Reasons to Explicitly Delete a Temporary Table:
Clarity and Best Practices: Including a DROP TABLE command for temporary tables is often a matter of clarity and good development practices. It clearly indicates to those reading the script that the table is no longer needed and that its lifecycle is being proactively managed.
Resource Management: Although SQL Server automatically manages the deletion of temporary tables, explicitly deleting them as soon as they are no longer needed can help free up resources, such as memory and disk space in tempdb, more quickly, especially in long sessions or transactions.
Preventing Conflicts in Long Sessions: In particularly long or complex sessions, where multiple operations are performed, explicitly deleting a temporary table as soon as it is no longer useful can help prevent conflicts or confusion with other parts of the script that might use temporary tables with similar names.
Habit and Portability: For developers working with different database management systems (DBMS), the habit of explicitly deleting all tables, including temporary ones, can be a common practice. This ensures some uniformity in resource management, regardless of the specifics of each DBMS.
Comments