Different Ways to Find SQL Server Orphaned Users
By: Jeffrey Yao | Comments (7) | Related: > Security
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?
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.
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:
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';
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.
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.
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:
- Understanding and dealing Orphaned Users in SQL Server databases
- Script to Drop All Orphaned SQL Server Database Users
- Identify Orphaned Windows Logins and Groups in SQL Server with sp_validatelogins
- Script to Find and Drop All Orphaned Users in All SQL Server Databases
About the author
View all my tips