Part of the DBA-Tools Project.
An orphaned user is a database user whose SID no longer matches any server login, usually left behind after a database restore or attach on a different server, a migration, or a login that was dropped without cleaning up the database users mapped to it. The user still exists in the database and still holds whatever permissions it had, but nothing can authenticate as it anymore, until someone notices a login failure and traces it back.
This script scans every online user database for exactly that mismatch and returns each orphaned user with the database it’s in and when it was created.
Why Orphaned Users Matters
Orphaned users are a very common, very quiet side effect of routine operations that nobody thinks to check afterward:
- The most common cause is restoring or attaching a database on a different server than it was created on, where the SIDs don’t line up with local logins
- An orphaned user causes a login failure the moment someone tries to connect as that identity, often discovered during an incident, not proactively
- They’re also a security review item: an orphaned user is dead weight in the permission model and worth cleaning up on principle
- Fixing one takes seconds once you know it’s there,
ALTER USER ... WITH LOGIN, the hard part is finding it in the first place
When to Run This Script
- After any database restore or attach, especially across servers or environments
- Routine SQL Server health checks
- After a migration or server move
- Security reviews and access audits
The Script
Run the following script against your SQL Server instance.
/*
Script Name : Get-OrphanedUsers
Category : security-and-permissions
Purpose : Find database users with no matching server login — common after migrations or login drops.
Author : Peter Whyte (https://sqldba.blog/dba-scripts-get-orphaned-users/)
Requires : VIEW ANY DATABASE
HealthCheck : Yes
Notes : Orphaned users cause login failures for that account. Fix with
ALTER USER [username] WITH LOGIN = [login_name]; or DROP USER [username].
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
IF OBJECT_ID('tempdb..#orphaned') IS NOT NULL DROP TABLE #orphaned;
CREATE TABLE #orphaned (
database_name NVARCHAR(128),
user_name NVARCHAR(128),
user_type NVARCHAR(60),
create_date DATETIME
);
DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += N'
USE ' + QUOTENAME(name) + N';
INSERT INTO #orphaned
SELECT
DB_NAME() AS database_name,
dp.name AS user_name,
dp.type_desc AS user_type,
dp.create_date
FROM sys.database_principals AS dp
WHERE dp.type IN (''S'', ''U'')
AND dp.principal_id > 4
AND dp.sid IS NOT NULL
AND dp.name NOT IN (''guest'', ''INFORMATION_SCHEMA'', ''sys'', ''dbo'')
AND NOT EXISTS (
SELECT 1
FROM sys.server_principals AS sp
WHERE sp.sid = dp.sid
);
'
FROM sys.databases
WHERE database_id > 4
AND state_desc = 'ONLINE';
EXEC sys.sp_executesql @sql;
SELECT
database_name,
user_name,
user_type,
create_date
FROM #orphaned
ORDER BY database_name, user_name;
DROP TABLE #orphaned;
The script iterates every online user database, comparing each database user’s SID against sys.server_principals, and returns any that have no match, along with the user’s type and creation date.
How To Run From The Repo
Clone DBA Tools, initialize and run the script:
# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools
# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1
# Find orphaned database users across all databases:
.\run.ps1 Get-OrphanedUsers
# To run against a remote sql server:
.\run.ps1 Get-OrphanedUsers -ServerInstance SQLSERVER01
This script lives in the repo at:
Example Output
This is a genuine finding on this lab instance, not staged: DemoSeverityUser in DemoDatabase, created back in February, with no matching server login today.
Understanding the Results
- database_name / user_name — exactly where the orphaned user lives. Every row here is a user that can no longer authenticate.
- user_type —
SQL_USER(SQL authentication) orWINDOWS_USER(Windows authentication). SQL users orphan more easily, since they’re tied to a SID with no directory backing; Windows users can orphan too if the underlying AD account is renamed or the domain changes. - create_date — how long the user has existed. An old creation date with no matching login suggests this has been broken for a while without anyone noticing.
How to Fix Orphaned Users
If a login with the right name already exists on the server, remap the user to it directly:
USE [DemoDatabase];
ALTER USER [DemoSeverityUser] WITH LOGIN = [DemoSeverityUser];
If no matching login exists yet, create one first (matching the original SID if you need identical permissions history), then remap:
-- Create a SQL login and map it in one step (new login, no pre-existing SID to match)
USE [DemoDatabase];
CREATE LOGIN [DemoSeverityUser] WITH PASSWORD = 'ChangeThisPassword!';
ALTER USER [DemoSeverityUser] WITH LOGIN = [DemoSeverityUser];
If the user is no longer needed at all:
USE [DemoDatabase];
DROP USER [DemoSeverityUser];
Best Practices
- Run this script as a standard step after every database restore or attach onto a different server.
- Prefer Windows authentication or contained database users where practical; both avoid the SID-matching problem that causes most orphaning.
- Review orphaned users as part of routine access audits, not just when a login failure forces the investigation.
Related Scripts
You may also find these scripts useful:
- Security (hub)
- Permissions and Role Membership
- Login Security Audit
- Sysadmin Members
- User Permissions Audit
Frequently Asked Questions
Why does a database restore create orphaned users?
Because a SQL login’s SID is generated when the login is created on that specific server. A database user’s SID is copied from the login at the time it was granted access. Restore that database on a different server, and unless a login with the exact same SID exists there too, the SIDs won’t match, and the user is orphaned.
Does WITH LOGIN change the user’s permissions?
No. ALTER USER ... WITH LOGIN only remaps which login the existing user maps to; the user keeps whatever permissions and role memberships it already had.
Summary
Orphaned users are one of the most common, least dramatic side effects of restoring or moving a database, easy to create by accident and easy to fix once you know they’re there. The hard part is knowing, which is exactly what this script solves.
Run it after every restore or attach onto a different server, and periodically as part of routine access reviews to catch anything that slipped through.
Leave a Reply