Different Ways to Find SQL Server Orphaned Users

By: Jeffrey Yao | Comments (7) | Related: > Security

Problem

SQL Server orphaned users are a common thorny issue in auditing. Microsoft has an article Troubleshoot Orphaned Users (SQL Server) that addresses one scenario, i.e. a SQL login (i.e. the login is not created from windows user / certificate / asymmetric key).

But in reality, there are multiple scenarios that can make things complex (excluding contained databases), such as:

A login [L] is created from a windows account [W], and a database user (U) is created from this login [L], but later the login is dropped.
A login [L] is created from a certificate or an asymmetric key, and a database [U] is create from this login [L], but later [L] is dropped.
A login is (L) created from a windows account [W], and a database user [U] is created from this login [L], but later the login is dropped, however, there is a login [LG] crated from a Windows group [G], of which [W] is a member.
A database user is created without login, i.e. like the following:
```
create user userNoLogin without login
```
A login [L] is created from a certificate [C], and then a database user [U] is created from this login [L], but now the certificate [C] expires.
A login [L] is created from a window account [W], also a login [LG] is created from a window group, of which [W] is a member, and a database user [U] is created from the login [L], now [L] is dropped, [U] does not have a corresponding login directly, but since [LG] exists, [U] should not be considered as an orphaned user, but how do we single out [U] as not orphaned?
A login [L] is created from a windows account, which is disabled or removed from domain controller. If so, any database user created from this [L] login should be considered orphaned.
A login [L] is created from a window account [W], also a login [LG] is created from a window group, of which [W] is a member, and a database user is created from the login [LG].

In such cases, how do we identify an orphaned user?

Solution

Microsoft has sp_change_users_login to report an orphaned user, but this stored procedure cannot handle any of the cases mentioned above. We can use sp_helptext sp_change_users_login to find out why.

sp_change_user_login - Description: check SP content

It is obvious that sp_change_users_login ‘report’ only deals with SQL users, which are created from standard SQL logins.

Now we will tackle cases mentioned in the problem statement.

SQL Server Orphaned Users Cases 1, 2, 3 and 4

A database user from a windows/certificate/asymmetric key login or no login, when the login is missing, we consider such database users as an orphaned users.

We first purposely create a few orphaned users as follows:

-- test case orphaned users from windows/certificate/asymmetric_key login
use master;
 
create certificate cerTest 
   ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'  
   WITH SUBJECT = 'Cert Test',   
   EXPIRY_DATE = '2027-11-12';
 
create login cerLogin from certificate cerTest;
 
-- create an asymmetric key and a login
CREATE ASYMMETRIC KEY AKTest   
    WITH ALGORITHM = RSA_2048   
    ENCRYPTION BY PASSWORD = '<enterStrongPasswordHere>';
 
create login akLogin from asymmetric key AKTest;
 
-- create a windows login
create login [ABC\jyao] from windows
 
--create a standard sql login
create login sqlLogin with password='He110WorlD', check_policy=off;
go
 
use MSSQLTips
create user cerUser from login cerlogin;
create user akUser for login akLogin;
create user [ABC\jyao] for login [ABC\jyao];
create user sqlUser for login sqlLogin;
 
-- create a user without login
-- this user is NOT an orphaned user
create user userNoLogin without login;
 
--now we drop those logins, so we create all the orphaned users
drop login cerLogin;
drop login akLogin;
drop login [ABC\jyao];
drop login sqlLogin;

We can use this to find the orphaned users:

-- Figure 1 code

use MSSQLTips
-- find orphaned users from windows/certificate/asymmetric_key login
select  dp.name, dp.type, dp.sid, LEN(dp.sid) as [SID_Len] 
from sys.database_principals dp
left join sys.server_principals sp
on dp.sid = sp.sid
left join sys.certificates c
on dp.sid = c.sid
left join sys.asymmetric_keys a
on dp.sid = a.sid
where sp.sid is null and c.sid is null and a.sid is null
-- check dp.type, go to the following
--https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-database-principals-transact-sql
and dp.type in ('U', 'S', 'C', 'K') 
and dp.principal_id > 4 -- 0..4 are system users which will be ignored
and not (dp.type = 'S' and LEN(dp.sid) = 28) -- to filter out the valid db users without login

What we expect is we will get all users except for the valid [userNoLogin]. After executing the Figure 1 code, we get:

Orphaned user - Description: result of orphaned users

That’s exactly what we expect.

SQL Server Orphaned Users Case 5

If a user is created from a login and the login is created from a certificate, but the certificate expires, whether considering the user as orphaned or not is up to the auditing requirement. But to me, such a user should be considered as orphaned in the sense, the user account should be removed or fixed just as those regular orphaned users.

We can use the following code to find such users:

-- Figure 2 code

USE MSSQLTips
select [DBUser]= dp.name
, [certificate]=mc.name
, mc.expiry_date
, curr_date = GETDATE()
from sys.database_principals dp
inner join master.sys.certificates mc
on dp.sid = mc.sid
where mc.expiry_date < getdate();

In my environment, I get the following:

SQL Server Orphaned Users Case 6

If a database user’s original windows login does not exist, but the original login’s window group exists, this database users should not be considered as orphaned, the previous Figure 1 code will have a false alarm in such cases. Actually, we need a short script instead of a query to handle this case.

We will first look at the following results after each event.

-- we first create a window login and a corresponding database user
use MSSQLTips;
create login [abc\jyao] from windows;
create user [abc\jyao] from login [abc\jyao];

Now if we run the following:

use MSSQLTips;
exec xp_logininfo 'abc\jyao';

We get:

Now if drop the login and re-run xp_logininfo:

drop login [abc\jyao];
exec xp_logininfo 'abc\jyao';

We will get nothing as shown below:

Now, we will create a login from windows group [ABC\Prod_DBAs], which contains [ABC\jyao], and then re-run xp_logininfo:

-- we will create a window group login and a corresponding database user
use MSSQLTips;
create login [abc\Prod_DBAs] from windows;
create user [abc\Prod_DBAs] from login [abc\Prod_DBAs];
exec xp_logininfo 'abc\jyao';

We will get:

But the login [abc\jyao] has already been dropped.

So with this basic knowledge, we can come up with the following algorithm to filter out any database users whose SID is a member of a windows group account, and there is a login created out of this windows group account.

Find orphaned users whose type is ‘U’, i.e. windows user account
Run xp_logininfo against each user from step 1
If it returns a row, it means this candidate is NOT orphaned. Otherwise, it is orphaned.

So here is the code:

-- Figure 3 code

-- find real orphaned windows user
set nocount on;
declare @name varchar(128);
declare @t table (name varchar(128))
declare @tmp table (acct varchar(128), type varchar(20)
, privilege varchar(20), mlogin varchar(128)
, permission varchar(128));
 
declare @c cursor;
set @c = CURSOR for
select  dp.name 
from sys.database_principals dp
left join sys.server_principals sp
on dp.sid = sp.sid
where dp.type ='U' -- only for window users
and dp.principal_id > 4; -- 0..4 are system users which will be ignored
 
open @c;
fetch next from @c into @name;
while @@FETCH_STATUS = 0
begin
   begin try
      insert into @tmp exec xp_logininfo @name;
      if @@ROWCOUNT = 0 
         insert into @t (name) values (@name);
   end try
   
   begin catch
      insert into @t (name) values (@name);
   end catch 
   
   fetch next from @c into @name; 
end
select * from @t

SQL Server Orphaned Users Cases 7 and 8

It is a little lengthy (for case 7) and impossible (for case 8, IMHO) to solve the problem with T-SQL. It would be much easier to rely on PowerShell and SMO. It really deserves another tip to have a detailed explanation about how to do it. In this tip, I will just list the technical keys to solve the issue.

Microsoft has a PowerShell module called “ActiveDirectory”, in which there are a few cmdlets related to our solution.
Get-ADUser to find the status of a window account, such as whether it exists or is enabled or not.
Get-ADGroup to find the status of a window group account, such as whether it exists or is enabled or not.
Get-AdGroupMember to find what member accounts the group has. (Note: A group can contain another subgroup).
Get-ADPrincipalGroupMemberShip to find what groups a member account belongs to.
In SMO, login class has a method EnumDatabaseMappings, so once we find a window login whose window account does not exist or is disabled, or we can use this EnumDatabaseMappings to find all database users that should be classified as orphaned.

Summary

Auditing orphaned users is a common task in auditing assignments. In this tip, we reviewed some complex scenarios related to orphaned database users and we also gave scripts to find these orphaned users. The scripts have been tested in SQL Server 2016/2017 environments and should be applicable to SQL Server 2008 and forward.

Actually, with the growth of SQL Server, the scenarios for an orphaned database user increases as well. For example, in SQL Server 2000, there is no user created from a certificate or asymmetric key. In SQL Server 2005, there is no user created without a login. As such, we may keep on updating our orphaned database user detection script to cover these and other scenarios.

If we look at sys.database_principals, its column [type] has two values that I have not worked with (as highlighted below).

It would be interesting to see what orphaned users look like and how to detect them in these two scenarios.

BOL picture - Description: snapshot from BOL

Next Steps

If you have an environment to test database users with type ‘E’ or ‘X’ (as listed above), please elaborate how orphaned users are generated and how to detect them.

You can also check the following articles to better understand how orphaned users are handled previously:

About the author

Jeffrey Yao is a senior SQL Server consultant, striving to automate DBA work as much as possible to have more time for family, life and more automation.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips