Query To List All Orphaned Logins In SQL Server

Hi there, being a DBA you must be dealing with orphaned users often but rarely with orphaned logins. In this article I am gonna provide you a script to list all orphaned logins in a SQL Server Instance.

Introduction:

First of all let us understand what are orphaned logins in SQL Server. The logins which does not have access to any database or server roles. Now next question is how orphaned login occurs in SQL Server Instance. In general it happens when you migrate or decommission database(s).

Few of the reasons are mentioned as follows:

Migration of database(s) leaving the associated logins.
Decommission of database(s) leaving the associated logins.
Deleting a server or database role that the login is a member of.
Moving user account from one domain to another.

Over time your server can become cluttered with orphaned logins and make it difficult for DBAs to manage. Hence it is important to regularly check and remove them. Obviously you need to follow the process as per your organization.

Query to list all orphaned logins in SQL Server:

DECLARE @dbname VARCHAR(50)  
DECLARE @statement NVARCHAR(MAX)

CREATE TABLE #database_sids
(
    [sid] int NOT NULL
);

DECLARE db_cursor CURSOR 
LOCAL FAST_FORWARD
FOR  
SELECT name
FROM master.sys.databases
WHERE  state_desc='online' 
OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @dbname  
WHILE @@FETCH_STATUS = 0  
BEGIN

SELECT @statement = 'select sid from ['+@dbname +'].sys.database_principals where sid is not Null and type!=''R'''

INSERT INTO #database_sids ([sid])
EXEC sp_executesql @statement

FETCH NEXT FROM db_cursor INTO @dbname  
END  
CLOSE db_cursor  
DEALLOCATE db_cursor

SELECT sp.name
    , sp.type_desc
FROM sys.server_principals sp
WHERE NOT EXISTS (
    SELECT 1
    FROM #database_sids ds
    WHERE ds. sid = sp. sid
    )
    AND sp.[type_desc] NOT IN (
          N'SERVER_ROLE'
        , N'CERTIFICATE_MAPPED_LOGIN'
        , N'ASYMMETRIC_KEY_MAPPED_LOGIN'
        )
        AND sp.name not in(
       SELECT P.name 
       FROM sys.server_role_members RM
       INNER JOIN sys.server_principals P ON RM.member_principal_id = P.principal_id)

drop table #database_sids

Sample Output:

Orphaned Logins

First thing to remember is not to delete them immediately. As can be seen in the above example, ‘NT AUTHORITY\SYSTEM’. That’s a critical account to verify before deleting. Hence it is important to realize that though the logins are Orphaned you need to do a though check before taking any action.

Conclusion:

In summary orphaned logins can occur due to number of reasons. Primary reasons being database migration, decommission or moving user from one domain to another. Point often overlooked is Orphaned Logins can pose security risk and hinder SQL Server management. However it is possible to identify and address them as per your organization standards. I hope the above script can help in your journey. Please let me know by leaving your comments below.

Please spread the word:

https://sqlgeekspro.com/query-to-list-all-orphaned-logins-in-sql-server/