Different Ways to Find SQL Server Orphaned Users

By:   |   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:

  1. 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.
  2. 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.
  3. 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.
  4. A database user is created without login, i.e. like the following:

    create user userNoLogin without login
  5. 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.
  6. 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?
  7. 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.
  8. 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:

expired certificate

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:

xp_logininfo result 1

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:

account name

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:

xp_logininfo result 2

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.

  1. Find orphaned users whose type is ‘U’, i.e. windows user account
  2. Run xp_logininfo against each user from step 1
  3. 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.

  1. Microsoft has a PowerShell module called “ActiveDirectory”, in which there are a few cmdlets related to our solution.
  2. Get-ADUser to find the status of a window account, such as whether it exists or is enabled or not.
  3. Get-ADGroup to find the status of a window group account, such as whether it exists or is enabled or not.
  4. Get-AdGroupMember to find what member accounts the group has. (Note: A group can contain another subgroup).
  5. Get-ADPrincipalGroupMemberShip to find what groups a member account belongs to.
  6. 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:



sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Jeffrey Yao 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



Comments For This Article




Wednesday, January 3, 2018 - 2:20:34 PM - Lee Back To Top (74718)

 Thank you sir for clarifying that. It makes better sense to me now. Thanks again.

Lee


Wednesday, January 3, 2018 - 12:12:26 PM - jeff_yao Back To Top (74714)

 

Hi Lee,

Thanks for your feedback. But if you read my case#6, what you see is exactly what I demo'ed, i.e. if we first create a login [L] from a windows account (not windows group account, say abc\L), and then in a database, create a user [U] for this login [L], now if we drop this login [L], this database user [U] will be orphaned. However, if now, I create another login [LW] from a window gorup account [abc\prod_dbas] of which [abc\L] is a member. The database user [U] will be not orphaned (even though we did not add back login [L]), and we do not need to add a database user for login [LW].

Hope this clarifies your concern. But let me know if you have any other questions.

 

Thanks again for your comment.

Kind regards

Jeffrey


Wednesday, January 3, 2018 - 9:32:44 AM - Lee Back To Top (74709)

 Jeffrey,

Thanks for taking the time to shre this code and information. I am however unable to make the code for Scenario #6 work correctly. If I create the Windows group login, create login [abc\Prod_DBAs] from windows; BUT do NOT add that to the user database and run xp_logininfo 'abc\jyao' I will get the results showing that it a member of the Windows group and therefore NOT an orphaned user for the database.

Thanks again. I have been able to make good use of your code.

Lee

 

 


Friday, December 1, 2017 - 6:07:01 PM - Greg Robidoux Back To Top (73546)

Hi Jeffrey,

this has been upated.

Greg


Friday, December 1, 2017 - 5:08:25 PM - jeff_yao Back To Top (73541)

Thanks Greg.You only removed out the fetch line, but I also need to change the "if @@ROWCOUNT > 0" to "if @@ROWCOUNT = 0"  i.e. it should be an equal sign (=) instead of of a big then (>) sign. 

 

Thanks,

Jeffrey

 


Friday, December 1, 2017 - 4:30:07 PM - Greg Robidoux Back To Top (73539)

Hi Jeffrey,

I updated the code.  Let me know if this looks OK.

Thanks
Greg


Friday, December 1, 2017 - 1:45:58 PM - jeff_yao Back To Top (73532)

 Hi Greg,

My code in Figure-3 actually has some typos in two lines. Please help to fix.

The original code is

if @@ROWCOUNT > 0 
insert into @t (name) values (@name);
fetch next from @c into @name;

Should be
if @@ROWCOUNT = 0 
insert into @t (name) values (@name);
-- fetch next from @c into @name;
The line
fetch next from @c into @name;
should be commented out or removed.

My sincere apologies.

Thanks,
Jeffrey














get free sql tips
agree to terms