DBA Repository – Find Orphaned Users

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.

Package Logic

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:

DBARepository_FindOrphanedUsers SSIS Package

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:

  1. Load all database owners into our DatabaseUsers staging table
  2. Loop through all databases on the instance and add all database users to the DatabaseUsers staging table
  3. 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:
    1. The user owns a scheduled job (the job must receive a new owner before the user is deleted)
    2. The user owns a schema that owns database objects (the schema must receive a new owner before the user can be deleted)
    3. 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
    4. 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.

Summary

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.

2 Responses to DBA Repository – Find Orphaned Users

  1. Rick says:

    Hi David,
    Thanks for this great article. I have a question and hope you can answer it. Is there a way to add a step in your SSIS package to identify the users who are valid AD domain users but have not been in the SQL Server for a long time? (e.g. 6 months). Thanks. Rick

  2. I suppose you could modify the queries in the DBA Repository that monitor the connection information, see this article. The sys.sysprocesses view has the login name, so that could be recorded along with the timestamp when you recorded the connection as active. This will not be perfect though, if a user connects then drops between your monitoring interval you will not record the fact that the connection was made.

Leave a Reply

Your email address will not be published. Required fields are marked *