Introduction

This article came about as the result of two previous articles I had written that this wonderful site published:Liberating the DBA from SQL Authentication with AD Groups and SQL Authentication Via AD Groups Part II: Who has what access? In these pieces, I push for the elimination of SQL authentication as rapidly as possible. In the second article I presented a SQL script that would find the ultimate users of the server and every database, even if abstracted by many levels of Active Directory groups.

Recently, while refreshing a lower environment with a (data-scrubbed of course) copy of production, I encountered an old friend: the orphaned database user. For those of us who are (like the most experienced of us used to be) newbies, an orphaned database user is a user record on a database that has no corresponding SQL Server login. This happens if one of two things has happened:

• The database was restored from a server which had the login, but the same login does not appear in the new database; or
• The login was deleted without removing its database users first. Even though Management Studio is quite diligent to remind us of this issue, it happens.

Ha Ha! I caught you in a Mistake! – Understanding SID

I can see many DBA clasping their hands and readying a comment for the discussion thread! “But what about the same named SQL Login? They don’t match between servers, either!” That is true if you created the logins manually between servers. SQL Logins created by hand in both machines will have a different Security Identifier (SID), and a restore will not recognize “Joe” on Server1 as “Joe” on Server2.

Not so fast, genius! The SID is the actual key of the SQL login and the database user. It is generated by SQL Server for SQL logins, but is received from Active directory for windows logins – including AD groups.

With SQL Logins, it is possible to script out the SQL logins in one server with the SID information and to add in those logins with matching SID information to another server, thereby avoiding this problem.

With AD database users, you can add the AD user as a login and the connection between SQL Server and the database is reinstated – the orphan is suddenly adopted! What does that mean? Picture developers creating a new application for payroll. The AD Group “DOMAIN\DEVELOPERS” is created, and given DBO access in development.

As the project progresses, the database (or at least its schema, perhaps kept in a Schema master empty database) is restored up into higher environments. First QA, then System Test and then into Production. DOMAIN\DEVELOPERS is an orphaned database user in all environments, with DBO access. Obviously, DOMAIN\DEVELOPERS ought not to have a SQL login. As long as it doesn’t, there is no access for the developers to the production database.

However, if that login is created, even with a simple statement like this:

CREATE LOGIN [DOMAIN\DEVELOPERS] FROM WINDOWS
 WITH DEFAULT_DATABASE = [tempdb], DEFAULT_LANGUAGE = [us_english];

The developers instantly have DBO access to production payroll! There is no warning message. It may be innocently done: For example, a developer needs read-only access to another database for testing. However, that developer will instantly see in Management Studio that he or she has payroll access.

If you still dream of that new Tesla, you need to be able to see and prevent the security exposure that this umm, “feature,” can bring.

Detect the Exposure

After seeing orphaned users – in my case production database users orphaned in a lower environment – I decided to improve the access script to detect orphaned users. I added SID because SQL Logins can have the same names and different SID across servers.

The end result of all of my neuroses is Version 2.5 of the SQL Blimp AD Access Identification Script. Here it is:

------------------------------------------------------------
-- The SQLBlimp AD Access Identification Script Version 2.5
-- By John F. Tamburo 2016-06-30
-- Feel free to use this - Freely given to the SQL community
------------------------------------------------------------
set nocount on;
declare @ctr nvarchar(max) = '', @AcctName sysname = '', @x int = 1
-- Create a table to store xp_logininfo commands
-- We have to individually execute them in case the login no longer exists
create table #ExecuteQueue(
AcctName sysname
,CommandToRun nvarchar(max)
,RowID int identity(1,1)
);
-- Create a command list for windows-based SQL Logins
insert into #ExecuteQueue(AcctName,CommandToRun)
SELECT 
[name]
,CONVERT(NVARCHAR(max),'INSERT INTO #LoginsList EXEC xp_logininfo ''' + [name] + ''', ''all''; --insert group information' + CHAR(13) + CHAR(10)
+ CASE 
WHEN [TYPE] = 'G' THEN ' INSERT INTO #LoginsList EXEC xp_logininfo  ''' + [name] + ''', ''members''; --insert member information'  + CHAR(13) + CHAR(10)
            else '-- ' + rtrim([name]) + ' IS NOT A GROUP BABY!' + CHAR(13) + CHAR(10)
        END) as CMD_TO_RUN
FROM sys.server_principals 
WHERE 1=1
and TYPE IN ('U','G')    -- *Windows* Users and Groups.
and name not like '%##%' -- Eliminate Microsoft 
and name not like 'NT SERVICE\%' -- xp_logininfo does not work with NT SERVICE accounts
ORDER BY name, type_desc;
-- Create the table that the commands above will fill.
create table #LoginsList(
       [Account Name] nvarchar(128),
       [Type] nvarchar(128),
       [Privilege] nvarchar(128),
       [Mapped Login Name] nvarchar(128),
       [Permission Path] nvarchar(128));
-- Jeff Moden: I got rid of the cursor!  Be Proud!  
-- I couldn't get rid of the loop since I have to error handle each SQL command for accurate results. :(
set @x=1
while @x=1
begin
select 
top 1 
@ctr = CommandToRun
from #ExecuteQueue
order by RowID;
IF @@ROWCOUNT = 0
set @X=0
ELSE
BEGIN
BEGIN TRY
print @ctr
EXEC sp_executesql @ctr
END TRY
BEGIN CATCH
print ERROR_MESSAGE() + CHAR(13) + CHAR(10);
IF ERROR_MESSAGE() like '%0x534%' -- Windows SQL Login no longer in AD
BEGIN
print '0x534 Logic'
insert into #LoginsList([Account Name],[Type],[Privilege],[Mapped Login Name],[Permission Path])
select @AcctName AccountName,'WINDOWS_USER','DELETED Windows User',@AcctName MappedLogin,@AcctName PermissionPath
END
ELSE
print ERROR_MESSAGE();
END CATCH;
with CTE as 
(
Select 
top 1 
RowId 
from #ExecuteQueue
order by RowID
)
delete from CTE;
END;
Print '-------------------------------'
END;

--add SID
alter table #LoginsList add sid varbinary(85);
-- Add SQL Logins to the result
insert into #LoginsList([Account Name],[Type],[Privilege],[Mapped Login Name],[Permission Path],[sid])
select 
[name] AccountName
,(case 
when [type] = 'S' then 'SQL_USER' 
when [type] = 'U' then 'WINDOWS_USER' 
when [type] = 'G' then 'WINDOWS_GROUP' 
else '?WTF' 
  END)
,'user'
,[name] MappedLogin
,[name] PermissionPath
,[sid]
FROM sys.server_principals 
WHERE 1=1
and (TYPE = 'S'     -- SQL Server Logins only
and name not like '%##%') -- Eliminate Microsoft 
or (TYPE in('U','G') /*and [name] like 'NT SERVICE\%'*/) -- capture NT Service information
ORDER BY [name];
-- Get Server Roles into the mix
-- Add column to table
alter table #LoginsList add Server_Roles nvarchar(max);

-- Fill column with server roles
update LL 
set 
Server_Roles = ISNULL(STUFF((SELECT ', ' + CONVERT(VARCHAR(500),role.name)
FROM sys.server_role_members
JOIN sys.server_principals AS role
ON sys.server_role_members.role_principal_id = role.principal_id
JOIN sys.server_principals AS member
ON sys.server_role_members.member_principal_id = member.principal_id
WHERE member.name= (case when [Permission Path] is not null then [Permission Path] else [Account Name] end)
FOR XML PATH('')),1,1,''),'public')
from #LoginsList LL;
-- Create a table to hold the users of each database.
create table #DB_Users(
DBName sysname
, UserName sysname
, LoginType sysname
, AssociatedRole varchar(max)
,create_date datetime
,modify_date datetime
,[sid] varbinary(85)
)
-- Iterate the each database for its users and store them in the table.
INSERT #DB_Users
EXEC sp_MSforeachdb
'
use [?]
SELECT ''?'' AS DB_Name,
ISNULL(case prin.name when ''dbo'' then (select SUSER_SNAME(owner_sid) from master.sys.databases where name =''?'') else prin.name end,'''') AS UserName,
prin.type_desc AS LoginType,
isnull(USER_NAME(mem.role_principal_id),'''') AS AssociatedRole ,create_date,modify_date, [sid]
FROM sys.database_principals prin
LEFT OUTER JOIN sys.database_role_members mem ON prin.principal_id=mem.member_principal_id
WHERE prin.sid IS NOT NULL 
and prin.sid NOT IN (0x00) 
and prin.is_fixed_role <> 1 
AND prin.name is not null
AND prin.name NOT LIKE ''##%'''
-- Refine the user permissions into a concatenated field by DB and user
SELECT
dbname
,username 
,[sid]
,logintype 
,create_date 
,modify_date 
,STUFF((SELECT ', ' + CONVERT(VARCHAR(500),associatedrole)
FROM #DB_Users user2
WHERE user1.DBName=user2.DBName 
AND user1.UserName=user2.UserName
FOR XML PATH('')),1,1,'') AS Permissions_user
into #UserPermissions
FROM #DB_Users user1
where logintype != 'DATABASE_ROLE'
GROUP BY
dbname
,username 
,[sid]
,logintype 
,create_date 
,modify_date
ORDER BY DBName,username;
-- Report out the results
with CTE as
(
Select 
DISTINCT
LL.[Account Name]
,LL.[sid]
,@@SERVERNAME as [Database Server]
,(case when UP.dbname is null then '[none]' else UP.DBName end) as [Database Name]
,( case when ll.Type = 'user' then 'WINDOWS_USER' else ll.type end) as LoginType
,LL.Privilege
,LL.[Server_Roles]
,LL.[Permission Path]
,UP.Permissions_user as [User Privileges]
from #LoginsList LL
left join #UserPermissions UU
on LL.[Account Name] = UU.UserName
left join #UserPermissions UP
on (LL.[sid] = UP.[sid] OR ((LL.SID is null) and LL.[Permission Path] = UP.[UserName]))
-- Comment out the where clause to see all logins that have no database users
-- and their server roles.
-- where exists(select 1 from #LoginsList U2 where U2.[sid] = UP.[sid])
union all
-- orphaned users
select 
UserName as [Account Name]
,A.[sid]
,@@SERVERNAME as [Database Server]
,DBName as [Database Name]
,LoginType
,'ORPHANED USER NO LOGIN' as Privilege
,'NONE' as [Server_Roles]
,null as [Permission Path]
,null as [User Privileges]
from #db_Users A
where 1=1
and A.LoginType != 'database_role'
--and A.LoginType != 'windows_group' -- groups no logins???
and UserName not like 'MS_%' -- no internal users
and not exists(select 1 from #LoginsList B where A.[sid] = B.[sid])
)
select 
distinct
* 
from CTE 
where 1=1
--and [LoginType] != 'Windows_Group'
--and [sid] is not null
and ([Permission Path] is not null or ([Permission Path] is null and [Privilege] like 'orphan%'))
order by 
[Account Name]
,[sid]
,[Database Name];
-- Clean up my mess
drop table #ExecuteQueue;
drop table #LoginsList;
drop table #DB_Users;
drop table #UserPermissions;

I also created a second script, also attached, that will look for and clean up orphaned users.

Here's that script:

----------------------------------------------------------------
-- The SQLBlimp Orphaned User ID and SQL Logins Cleanup Script
-- Version 1.0
-- By John F. Tamburo 2016-06-16
-- Feel free to use this - Freely given to the SQL community
-- SQL 2008 and Newer.
----------------------------------------------------------------
set nocount on;
declare @ctr nvarchar(max) = '', @AcctName sysname = ''
declare @OrphanDropSQL nvarchar(max) = '', @X int = 1;
-- Create a table to store xp_logininfo commands
-- We have to individually execute them in case 
-- a Windows login no longer exists in AD
create table #ExecuteQueue(AcctName sysname,CommandToRun nvarchar(max), RowID int identity(1,1));
-- Create a command list for windows-based SQL Logins
insert into #ExecuteQueue(AcctName,CommandToRun)
SELECT 
[name]
,CONVERT(NVARCHAR(max),'INSERT INTO #LoginsList EXEC xp_logininfo ''' + [name] + ''', ''all''; --insert group information' + CHAR(13) + CHAR(10)
+ CASE 
WHEN [TYPE] = 'G' THEN ' INSERT INTO #LoginsList EXEC xp_logininfo  ''' + [name] + ''', ''members''; --insert member information'  + CHAR(13) + CHAR(10)
            else '-- ' + rtrim([name]) + ' IS NOT A GROUP BABY!' + CHAR(13) + CHAR(10)
        END) as CMD_TO_RUN
FROM sys.server_principals 
WHERE 1=1
and TYPE IN ('U','G')    -- *Windows* Users and Groups.
and name not like '%##%' -- Eliminate Microsoft 
and name not like 'NT SERVICE\%' -- xp_logininfo does not work with NT SERVICE accounts
ORDER BY name, type_desc;
-- Create the table that the commands above will fill.
create table #LoginsList(
       UserName nvarchar(128),
       [Type] nvarchar(128),
       [Privilege] nvarchar(128),
       [Mapped Login Name] nvarchar(128),
       [Permission Path] nvarchar(128),
   [RowID] int identity(1,1)
);
-- Jeff Moden: I got rid of the RBAR!  Be Proud!
set @x=1
while @x=1
begin
select 
top 1 
@ctr = CommandToRun
from #ExecuteQueue
order by RowID;
IF @@ROWCOUNT = 0
set @X=0
ELSE
BEGIN
BEGIN TRY
print @ctr
EXEC sp_executesql @ctr
END TRY
BEGIN CATCH
print ERROR_MESSAGE() + CHAR(13) + CHAR(10);
IF ERROR_MESSAGE() like '%0x534%' -- Windows SQL Login no longer in AD
BEGIN
print '0x534 Logic'
insert into #LoginsList(UserName,[Type],[Privilege],[Mapped Login Name],[Permission Path])
select @AcctName AccountName,'user','DELETED Windows User',@AcctName MappedLogin,@AcctName PermissionPath
END
ELSE
print ERROR_MESSAGE();
END CATCH;
with CTE as 
(
Select 
top 1 
RowId 
from #ExecuteQueue
order by RowID
)
delete from CTE;
END;
Print '-------------------------------'
END;

--add SID
alter table #LoginsList add sid varbinary(85);
-- Add SID and SQL Logins 
insert into #LoginsList(UserName,[Type],[Privilege],[Mapped Login Name],[Permission Path],[sid])
select [name] AccountName,'user','user',[name] MappedLogin,[name] PermissionPath, [sid]
FROM sys.server_principals 
WHERE 1=1
and (TYPE = 'S'     -- SQL Server Logins
and name not like '%##%') -- Eliminate Microsoft 
or (TYPE in('U','G') ) -- Capture SID for AD Users and Groups
ORDER BY [name];
--assign a row ID
print 'Drop all windows logins where the Windows user has been deleted.'
--Drop all windows logins where the Windows user has been deleted.
set @x=1;
while @x=1
begin
select 
top 1
 @OrphanDropSQL = 'DROP LOGIN [' + UserName + '];'
from #LoginsList
where [Privilege] = 'DELETED Windows User'
order by RowID;
if @@ROWCOUNT = 0
set @x=0
ELSE
BEGIN
print @orphanDropSQL;
--exec(@OrphanDropSQL);
with CTE as
(
select 
top 1 
RowID 
from #LoginsList
where [Privilege] = 'DELETED Windows User'
order by RowID
)
delete from CTE;
END;
end;

-- now let's search the databases for orphans
create table #OrphansList(
DBName sysname
,UserName nvarchar(128)
,SID varbinary(85)
,RowID int identity(1,1)
);
-- Create a table to hold the users of each database.
create table #DB_Users(
DBName sysname
, UserName sysname
, LoginType sysname
,[sid] varbinary(85)
)

INSERT #DB_Users
EXEC sp_MSforeachdb
'
use [?]
if ''?'' not in (''msdb'',''model'') -- do not bother with system databases; delete line if you do want to bother.
BEGIN
SELECT ''?'' AS DB_Name,
ISNULL(case prin.name when ''dbo'' then (select SUSER_SNAME(owner_sid) from master.sys.databases where name =''?'') else prin.name end,'''') AS UserName,
prin.type_desc AS LoginType,[sid]
FROM sys.database_principals prin
LEFT OUTER JOIN sys.database_role_members mem ON prin.principal_id=mem.member_principal_id
WHERE prin.sid IS NOT NULL 
and prin.sid NOT IN (0x00) 
and prin.is_fixed_role <> 1 
AND prin.name is not null
AND prin.name NOT LIKE ''##%''
END
'
--select * from #DB_Users;
print 'Drop those orphan DB Users!'
insert into #OrphansList
select
DBName as [Database Name] 
,UserName as UserName
,[sid]
from #db_Users A
where 1=1
and A.LoginType != 'database_role'
--and A.LoginType != 'windows_group' -- groups no logins???
and UserName not like 'MS_%' -- no internal users
and not exists(select 1 from #LoginsList B where A.[sid] = B.[sid]);
--select * from #OrphansList;
set @x=1;
While @x=1
begin
select top 1 @OrphanDropSql = 
'Use [' + DBName + ']
IF EXISTS(select 1 from['+DBName+'].[sys].[schemas] where [name] = ''' + UserName +''')
DROP SCHEMA [' + UserName + '];
DROP USER [' + UserName + '];'
from #OrphansList
order by RowID;
if @@rowCount = 0
set @X=0
else
begin
print @OrphanDropSQL;
--exec(@OrphanDropSQL);
with CTE as
(
select 
top 1 DBNAME, UserName, SID
from #OrphansList
order by RowID
)
delete from CTE;
end;
end;
drop table #OrphansList;
drop table #DB_Users;
drop table #ExecuteQueue;
drop table #LoginsList;

In addition, the orphan-drop script will check for AD logins where the AD User no longer exists and drop those logins too. When you run it, the script will generate output to indicate that it found orphans and will attempt to drop those logins, reporting its progress. Run these scripts in Management Studio in Text mode.

-------------------------------
Drop all windows logins where the Windows user has been deleted.
Drop those orphan DB Users!
Use [XXXX_d01_next]
IF EXISTS(select 1 from[XXXX_d01_next].[sys].[schemas] where [name] = 'developer')
DROP SCHEMA [developer];
DROP USER [developer];

Now you won’t get weird questions from your auditors. If your shop or company is too small to be regularly audited (SOX and PCI compliance will inevitably give rise to an audit requirement), you can still run this for the sake of cleanliness and the peace of mind that the janitor won’t be posting the CEO’s salary on Snapchat.

Crafty Auditors: Take Notice! Oh Wait; Never Mind; Keep on Moving. Nothing to See Here…

If you are an auditor that has to check SQL Server for exposure, the Access Identification script is probably the most thorough examination of SQL Server security for any server version 2008 or newer. Try it out and compare its outputs against the little scripts that you hand off to DBA to check on access exposures.

Just kidding! Auditors should never use this script! Keep on moving. There's nothing to see here.  This is of no interest whatsoever to auditors! Of course, crafty DBA will be running this script regularly to keep things perfectly clean.

Conclusion

The cleaner your security, the safer your security. Orphan database users with no login, SQL database user orphans with the same name but a mismatched SID to the login, and Windows logins where the AD user is deleted all create little bits of dirt. These can nip enterprises at audit time, and can create real exposures. Best to clean up and beam with pride at your tidy SQL Server security environment.

----

John F. Tamburo is the Chief Database Administrator for Landauer, Inc., the world's leading authority on radiation measurement, physics and education. John can be found at @SQLBlimp on Twitter. John also blogs at www.sqlblimp.com.