SQL Server Blog

Handling Invalid and Orphaned Users in SQL Server

This post is part of our SQL Server security blog series, 30 SQL Server Security Checks in 30 Days. We’re publishing a new security check every day in the month of June. Visit our sp_CheckSecurity page to learn about our free SQL Server tool you can download and run to check your own server.


Understanding the intricacies of user access and permissions is crucial for a secure and reliable SQL Server instance. A couple of aspects that often go unnoticed until they become a problem are the concepts of orphaned and invalid users.

What is an Orphaned User in SQL Server?

In SQL Server, a user becomes ‘orphaned’ when it exists within a database but lacks an associated login at the server level. This typically occurs when a database is either moved or restored to a different SQL Server instance. To understand why, it’s important to note that while logins are created at the server level, users are created at the database level. Each login is linked to a unique Security Identifier (SID). Therefore, during the process of moving or restoring a database, the SIDs may not align correctly, resulting in orphaned users.

What is an Invalid User in SQL Server?

An invalid user in SQL Server is a user that has been created in a database but is not linked to a login at the server level. This can occur when a login is dropped from the server or when a database is restored to a different server where the login does not exist.

The Problem with Orphaned and invalid Users

Orphaned users can lead to a few issues. For instance, after attaching or restoring a database, previously created and configured logins in the database may not provide access, leading to ‘login failed’ errors. Additionally, when trying to add a login to the database, you may encounter a message stating, ‘the user already exists in the current database’. Orphaned users, despite losing login capabilities, still retain permissions and database/object access. On the other hand, invalid users have one main visible issue in that any agent jobs owned by the user will fail due to authentication issues. Fortunately, both are relatively easy to identify and fix.

Identifying orphaned and invalid users

I have created a script that will help identify invalid and orphaned users, as well as finding schema’s owned by the orphaned users. The remediation for orphaned users is to drop them from the database, re-link them to a server principal, creating a new one with the same SID, or creating a login for the windows user. In the case of recreating a SQL auth login, please make sure to adjust the password before running the provided create statement. If you are going to drop the orphaned user, the last section identifies any schemas owned by the user which will need to re-assigned before the drop can succeed.

For invalid users, the typical solution is to drop them from the instance and databases as well, unless it is a mistake that the account was deleted from the domain environment. Please run this on a test environment first and understand the implications of each of the possible actions you can take before doing so.

-- Ready to go at master level!

-- ALL results table
    IF OBJECT_ID('tempdb..#Results') IS NOT NULL
        DROP TABLE #Results;

    CREATE TABLE #Results (
        Importance int,
        [User] NVARCHAR(50),
        Script NVARCHAR(300),
        Alternate NVARCHAR(300),
-- Invalid Logins table
    IF OBJECT_ID('tempdb..#InvalidLogins') IS NOT NULL
        DROP TABLE #InvalidLogins;
    CREATE TABLE #InvalidLogins (
        LoginSID    varbinary(85),
        LoginName   NVARCHAR(100)

    INSERT INTO #InvalidLogins
    EXEC sp_validatelogins;

    Insert into #Results
    SELECT  0
    ,   LoginName
    ,   '/* Drop Invalid login */ USE master; DROP LOGIN ['+ LoginName+']; /* Login no longer present in windows.*/'
    ,   NULL
    FROM #InvalidLogins 

-- Orphaned User table
    IF OBJECT_ID('tempdb..#Orphaned') IS NOT NULL 
      DROP TABLE #Orphaned
    CREATE TABLE #Orphaned (UserName nvarchar(100), dbn nvarchar(100),IDENT INT IDENTITY(1,1))
    INSERT INTO #Orphaned (UserName, dbn)
    EXEC sp_MSforeachdb '
    USE [?]
    IF DB_Name() NOT IN (''tempdb'') BEGIN
    CAST([name] as NVARCHAR(100))
    , db_name()
    FROM sys.database_principals
    WHERE sid NOT IN (SELECT sid FROM sys.server_principals)
    AND authentication_type_desc = ''INSTANCE''
    AND type = ''S''
    AND principal_id != 2
    AND DATALENGTH(sid) <= 28

-- Orphaned User Fixes Script Generation


IF(SELECT COUNT(*) FROM #Orphaned) > 0
 SET @loop = 1
 WHILE @loop <= (SELECT MAX(IDENT) FROM #Orphaned)
    SET @USER = (SELECT UserName FROM #Orphaned WHERE IDENT = @loop)
    IF(SELECT COUNT(*) FROM sys.server_principals WHERE [Name] = @USER) <= 0
        IF EXISTS(SELECT 1 FROM sys.database_principals WHERE [Name] = @USER AND type_desc = 'WINDOWS_USER')
            Insert into #Results
            SELECT 1, @USER, '/* Re-adding the user */ CREATE LOGIN [' + @USER + '] FROM WINDOWS', NULL FROM #Results a WHERE NOT EXISTS (
        SELECT 1 FROM #InvalidLogins b WHERE a.[User] = b.LoginName )
        IF NOT EXISTS(SELECT 1 FROM sys.database_principals WHERE [Name] = @USER AND type_desc = 'SQL_USER')
            Insert into #Results 
            SELECT 1, @USER,'/* Re-creating the SQL user */ CREATE LOGIN [' + @USER + '] WITH PASSWORD = N''C0mPl3X$_p@s$w0Rd_ _FoRce_ R3$3t!!''' , NULL
    Insert into #Results 
    SELECT 2, @USER, '/*Re-linking users*/ USE [' + o.dbn + ']; ALTER USER [' + @USER + '] WITH LOGIN = [' + @USER + ']' , '/*Eliminating unneeded user*/ USE [' + o.dbn  + ']; DROP USER IF EXISTS [' + @USER + ']'  FROM #Orphaned o WHERE IDENT = @loop

    SET @loop = @loop + 1

SELECT DISTINCT Importance, @@SERVERNAME as [Server Name], [User] as [Orphaned User], Script, Alternate
FROM #Results a

ORDER BY importance

--- Check for any objects owned by these orphaned or invalid users before dropping

-- Object Ownership table
    IF OBJECT_ID('tempdb..#Ownership') IS NOT NULL
        DROP TABLE #Ownership;

    CREATE TABLE #Ownership (
        dbname NVARCHAR(100),
        objectname NVARCHAR(100),
        objecttype NVARCHAR(50),
        user_owner NVARCHAR(50),
        fix_script NVARCHAR(300),

--Database Schemas
    INSERT INTO #Ownership 
    EXEC sp_MSforeachdb '
    USE [?]
    IF DB_Name() NOT IN (''tempdb'') BEGIN
    ,   s.name  
    ,   ''Schema''
    ,   u.name
    ,   ''USE [''+ db_name() +''];
    ALTER AUTHORIZATION ON SCHEMA::[''+s.name +''] TO [dbo] /* previous owner [''+u.name+'']*/
    FROM sys.schemas s
    INNER JOIN sys.sysusers u ON u.uid = s.principal_id
    INNER JOIN #orphaned o on u.name COLLATE SQL_Latin1_General_CP1_CI_AS = o.UserName 
    ORDER BY s.name

SELECT * FROM #Ownership


Managing orphaned users is an essential part of SQL Server administration. Understanding what orphaned and invalid users are, how they are created, and how to handle them can prevent unexpected access issues and ensure smooth database operations.

David Seis
Article by David Seis
David Seis is a Support Technician that joined Straight Path in the summer of 2021. His goal is to work in an area that he believes helps others, and to do it to the best of his ability. He loves to help, solve problems, and make things easy for others to understand. Because of this he is lovingly known as a “Good helper” among friends and family. Outside of work, he has a growing passion for cybersecurity, specifically helping protect those who are most vulnerable to fraud, scams, and phishing. He looks forward to learning more about each of his clients, helping minimize issues, and ensure that any problems are solved quickly.

Subscribe for Updates


Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Share This