One of the important aspects of the DBA Repository is to notify when the data infrastructure may have a security problem. One thing that can increase the enterprise’s surface area for attack is orphaned users on the databases and orphaned logins on the SQL Server.
Orphaned logins often happen when a database is moved from one server to another, the login’s are created at the destination but are not removed from the source. Also, as databases are removed from enterprise due to end-of-life it is an easy thing to forget to remove the server logins that are no longer necessary. Another common orphan problem occurs when an employee leaves the organization and the Active Directory account is removed but the login remains on the SQL Server.
I created an SSIS package for the DBA Repository called DBARepository_FindOrphanedUsers that will scan all SQL Server instances and look for server logins or database users that appear to no longer be used so the DBA team can follow-up and eliminate the orphans.
For brevity I’ll only discuss the query that does the hard work below. The package performs the following logical steps:
- Remove Orphan User records from the DBA Repository – the entire dataset will be reloaded
- Find the list of server instances to scan
- Loop through each server instance and create a couple staging tables at the source instance. These tables will allow the package to compare database users to server logins to find which may be orphans
- Look for orphans – this step will be covered in detail below
- Transfer orphans to the DBA Repository
- Clean up staging tables
This is what the package looks like:
DBA Repository’s OrphanedUsers Table
The DBA Repository needs a table to hold the orphaned users found. I’ll store the server name, database name and user name where the user was found. The query that finds orphaned users performs a few checks and it will generate a message indicating what the problem with the user or login was and we’ll store that message as well. Here’s the table:
CREATE TABLE dbo.OrphanedUsers( OrphanedUserID int IDENTITY(1,1) NOT NULL, ServerName sysname NOT NULL, DatabaseName sysname NOT NULL, [User] sysname NOT NULL, Message varchar(max) NULL, Created datetime NULL, CONSTRAINT i01OrphanedUsers PRIMARY KEY NONCLUSTERED (OrphanedUserID ASC) ) GO ALTER TABLE dbo.OrphanedUsers ADD CONSTRAINT DF_Created DEFAULT (getdate()) FOR Created GO
Looking for Orphans
The query that finds orphaned users runs in the Load Staging Tables task. It performs the following steps:
- Load all database owners into our DatabaseUsers staging table
- Loop through all databases on the instance and add all database users to the DatabaseUsers staging table
- Find orphaned database users. The query loads a cursor with all database users that are not mapped to a server login. These are orphaned users and will be loaded to the Results staging table. There are caveats that will be noted for each user in the Results staging table if:
- The user owns a scheduled job (the job must receive a new owner before the user is deleted)
- The user owns a schema that owns database objects (the schema must receive a new owner before the user can be deleted)
- Find orphaned server logins. The query now looks for server logins that are not mapped to any database or owners of scheduled jobs. The results are stored in the Results staging table
- Find logins that are no longer in Active Directory. The last step in the query loads the Results staging table with Windows logins on the server that are no longer in Active Directory
Here’s the full query:
SET NOCOUNT ON BEGIN -- Load all database owners into a staging table INSERT dbo.cos_DatabaseUsers ( DatabaseName , DatabaseUser , [sid] , principal_id , [Type] ) SELECT DatabaseName = d.NAME , DatabaseUser = sp.NAME , [sid] = d.owner_sid , principal_id = 0 , [Type] = 'OWNER' FROM sys.databases AS d INNER JOIN sys.server_principals AS sp ON d.owner_sid = sp.sid -- Load all database users into a staging table EXECUTE sys.sp_MSforeachdb 'USE [?] INSERT [master].dbo.cos_DatabaseUsers ( DatabaseName , DatabaseUser , [sid] , principal_id , [Type] ) SELECT DatabaseName = CAST(DB_NAME() AS sysname) , DatabaseUser = CAST(dp.name AS sysname) , dp.sid , dp.principal_id , [Type] = ''USER'' FROM sys.database_principals AS dp WHERE dp.TYPE IN (''S'', ''U'', ''G'') -- Users Only AND (dp.principal_id BETWEEN 5 AND 16383 -- Ignore system reserved principals OR dp.principal_id >= 16400) AND dp.name NOT LIKE ''%'' + @@SERVERNAME + ''%'' ORDER BY dp.name' -- Find all database users that do not have a server login DECLARE @DatabaseName sysname , @DatabaseUser sysname , @principal_id int , @schema_id int , @sql varchar(max) DECLARE OrphanedUsers CURSOR FOR SELECT du.DatabaseName , du.DatabaseUser , du.principal_id FROM [master].dbo.cos_DatabaseUsers AS du LEFT JOIN sys.server_principals AS sp ON du.sid = sp.sid WHERE sp.NAME IS NULL AND du.[Type] = 'USER' AND du.DatabaseUser NOT LIKE 'MS_%' -- SQL 2008 msdb internal user ORDER BY DatabaseName OPEN OrphanedUsers FETCH NEXT FROM OrphanedUsers INTO @DatabaseName, @DatabaseUser, @principal_id WHILE @@FETCH_STATUS = 0 BEGIN SET @sql = ' USE [' + @DatabaseName + '] DECLARE @OwnsObjects int , @schema_name sysname SET @OwnsObjects = 0 -- Determining if the user owns any objects directly SELECT @OwnsObjects = COUNT(*) FROM sys.objects AS o WHERE o.principal_id = ' + CAST(@principal_id AS varchar) + ' -- Determining if the user owns a schema that owns an object SELECT @OwnsObjects = COUNT(*) FROM sys.objects AS o INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id WHERE s.principal_id = ' + CAST(@principal_id AS varchar) + ' -- User owns nothing flag him as deletable IF @OwnsObjects = 0 BEGIN INSERT [master].dbo.cos_Results ( DatabaseName , [User] , [Message] ) VALUES ( ''' + @DatabaseName + ''' , ''' + @DatabaseUser + ''' , ''Orphaned database user - user owns no objects'') END ELSE BEGIN INSERT [master].dbo.cos_Results ( DatabaseName , [User] , [Message] ) VALUES ( ''' + @DatabaseName + ''' , ''' + @DatabaseUser + ''' , ''Orphaned database user - user owns objects'') END' -- PRINT @sql EXECUTE (@sql) FETCH NEXT FROM OrphanedUsers INTO @DatabaseName, @DatabaseUser, @principal_id END DEALLOCATE OrphanedUsers -- Finding all non-system logins that are not mapped to any DB and don't own jobs DECLARE @Login sysname DECLARE Logins CURSOR FOR SELECT p.name FROM sys.server_principals AS p LEFT JOIN (SELECT DISTINCT DatabaseUser , [sid] FROM dbo.cos_DatabaseUsers) AS du ON p.sid = du.sid WHERE p.type IN ('S', 'U', 'G') -- Users only AND p.name NOT LIKE '%' + @@SERVERNAME + '%' -- Ignore users added by the installation AND p.name NOT LIKE '%distributor%' -- Ignore replication users AND p.name NOT LIKE '%BUILTIN%' -- Ignore the BUILTIN\Administrators AND p.name NOT LIKE '%NT AUTHORITY%' -- Ignore the Local System accounts AND p.name NOT LIKE '%NT SERVICE%' AND p.name NOT LIKE 'MS_%' -- SQL 2008 msdb internal user -- if you have specific logins that should be ignored add them here AND p.sid NOT IN (SELECT DISTINCT owner_sid FROM msdb..sysjobs) -- Ignore users that own jobs AND du.sid IS NULL ORDER BY p.name OPEN Logins FETCH NEXT FROM Logins INTO @Login WHILE @@FETCH_STATUS = 0 BEGIN INSERT dbo.cos_Results ( DatabaseName , [User] , [Message] ) VALUES ( 'SERVER LOGIN' , @Login , 'Unused server login') FETCH NEXT FROM Logins INTO @Login END DEALLOCATE Logins -- Find any logins that were removed from AD DECLARE @InvalidLogins TABLE ( [SID] varbinary(85) , NTLogin sysname ) INSERT @InvalidLogins EXECUTE sp_validatelogins INSERT dbo.cos_Results ( DatabaseName , [User] , [Message] ) SELECT 'SERVER LOGIN' , NTLogin , 'Login no longer in Active Directory' FROM @InvalidLogins END
Once the data has been load to the Results staging table it is transferred to the DBA Repository in the Transfer from Source Server to DBARepository Data Flow task and final housekeeping is completed for the package.
This package loops through all the server instances configured for monitoring in the DBA Repository to find all unused server logins and orphaned database users on each instance. The reason for each orphan login/user is recorded so clean up is simplified. That data is loaded to the DBA Repository and can be displayed in the dashboard for the DAB team to act on.
Cleaning up the orphaned user will reduce the surface area exposed by your SQL Server instances and keep your instance logins as minimal as possible and in sync with Active Directory.