Handling Invalid and Orphaned Users in SQL Server

Introduction

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!
SET NOCOUNT ON

-- 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
    SELECT 
    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
    END'

-- Orphaned User Fixes Script Generation

DECLARE @loop INT
DECLARE @USER sysname

IF(SELECT COUNT(*) FROM #Orphaned) > 0
BEGIN
 SET @loop = 1
 WHILE @loop <= (SELECT MAX(IDENT) FROM #Orphaned)
  BEGIN
    SET @USER = (SELECT UserName FROM #Orphaned WHERE IDENT = @loop)
    IF(SELECT COUNT(*) FROM sys.server_principals WHERE [Name] = @USER) <= 0
     BEGIN
        IF EXISTS(SELECT 1 FROM sys.database_principals WHERE [Name] = @USER AND type_desc = 'WINDOWS_USER')
         BEGIN
            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 )
         END
        IF NOT EXISTS(SELECT 1 FROM sys.database_principals WHERE [Name] = @USER AND type_desc = 'SQL_USER')
         BEGIN
            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
         END 
     END
    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
  END
END


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
    SELECT 
    db_name()
    ,   s.name  
    ,   ''Schema''
    ,   u.name
    ,   ''USE [''+ db_name() +''];
    GO
    ALTER AUTHORIZATION ON SCHEMA::[''+s.name +''] TO [dbo] /* previous owner [''+u.name+'']*/
    GO''
    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
    END'

SELECT * FROM #Ownership

SET NOCOUNT OFF

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.