Script : How to find SQL Server orphan Logins?


Dear Readers,

An attempt has been made to write a short script to find out SQL Server Orphan Logins. By the way, I am not sure if Microsoft has any term like “Orphan Login” within their documentation. However, I have seen that many people are talking about Orphan Logins but not orphan users. There is a difference between Orphan Login and Orphan users. In any case, lets call it “Orphan Login situation”

Orphan login situation –

A SQL Server Login may be an “orhpan” when there is no associated user/group at the AD Level/Server Level.

Orphan user –

User in the database is an “orphan” when there is no Login id associated with the user. This is true even if there is a login id that matches the user but there is no GUID (called a SID) that matches.

How to simulate Orphan Login Situation?

First create Local user and a Local Group at windows server.

Login to your window server > server manager > Configuration > Local users and Groups

  • Create one local user as servername\testorphanuser
  • Create on local group as servername\testorphangroup

Login to your SQL server and create two logins for your windows user and group like below:

  • servername\testorphanuser
  • servername\testorphangroup

Once you are done – you can delete these local user and windows group at windows level.

Open server manager > configuration > local user and groups > right click user and delete it.

Open server manager > configuration > local user and groups > right click group and delete it.

Now, at SQL Server, you have two orphan logins.

So in order to find the orphan logins, you can use the below script.

This script is a modified version of my previous script – Who all have access to SQL Server via AD Groups?

 

/* Written by Sachin Diwakar for www.dbathings.com*/
CREATE TABLE #holdLogins (

 accountname VARCHAR(max) 
 ,type VARCHAR(20)
 ,priv VARCHAR(20)
 ,mappedlogin VARCHAR(max)
 ,ADGroup VARCHAR(max) )
SET QUOTED_IDENTIFIER OFF
GO
DECLARE @user_name VARCHAR(100)
,@exec_sql VARCHAR(2000)
DECLARE user_cursor CURSOR FOR
/*change here if you are including AD Groups as well*/
--SELECT name FROM sys.syslogins WHERE (isntname = 1) 
SELECT name FROM sys.syslogins WHERE (isntname = 1 AND isntuser = 1) 
AND ( NAME NOT LIKE 'NT SERVICE%' and name not like 'NT AUTHORITY%')
OPEN user_cursor
FETCH NEXT
FROM user_cursor
INTO @user_name 
WHILE @@FETCH_STATUS = 0 
BEGIN
 SET @exec_sql = 'EXEC master..xp_logininfo @acctname = ''' + @user_name + ''''
BEGIN TRY 
 -- print @exec_sql 
 INSERT INTO #holdLogins 
 EXECUTE (@exec_sql) 
END TRY
BEGIN CATCH
 INSERT INTO #holdLogins
 VALUES ( @user_name
 ,'--NA--' 
 ,'--NA--' 
 ,'--Seems Orphan Login--' 
 ,@user_name 
 )
 END CATCH
FETCH NEXT
 FROM user_cursor
 INTO @user_name
END
CLOSE user_cursor
DEALLOCATE user_cursor
SELECT * FROM #holdLogins
DROP TABLE #holdLogins

Output :
orphanLogin

 

 

 

I tried this script with couple of scenarios only. However, I would encourage if you can comment here and share your feedback if this works for you. I hope this helps..