Orphaned database users

Job details

Parameters

Job Summary

• Purpose: The purpose of this job is to monitor and check for the presence of orphaned database users in a SQL Server environment.
• Why: This job is important because it helps identify any database users that do not have a corresponding SQL Server login, effectively managing access control and improving security. Orphaned users can pose security risks since their associated logins might have been deleted or improperly managed.
• Manual checking: You can check for orphaned users manually in the database by issuing these SQL commands:

SELECT dp.name AS UserName, dp.type_desc AS UserType
  FROM sys.database_principals AS dp
  LEFT JOIN sys.server_principals AS sp ON dp.sid = sp.sid
  WHERE sp.sid IS NULL AND dp.type IN ('S', 'U', 'G')

Implementation Details

• The job includes monitoring SQL procedures that check each user in all databases against server principals to see if any are orphaned.
• If the parameter “revoke CONNECT“ is set to “YES,” the job will automatically revoke the CONNECT permission for these users, enhancing database security.
• The job involves several dependencies, including the main checking procedure and tables for storing current status and historical data about orphaned users.

Reporting Features

• The job provides a report which lists all databases along with orphaned users and their access details.
• The report includes detailed information such as the database name, orphaned user, default schema, database access status, how often the check has been performed, and the last check date.
• Additionally, an aggregated statistics graph visualizes the checks performed over time for orphaned users, allowing for easy tracking of these instances over time.

Dependency Tables

Presentation Details

• The monitoring job’s report is designed to provide easy-to-understand visual and tabular presentations of the data.
• The report template specifies the columns for the data shown, such as database name, user, default schema, and whether the user has database access.
• For aggregated statistics, a category chart is included that plots the number of times checks were made against the historical dates.

Enhanced Functionality in Updates

• The monitoring job ensures that it remains compatible with newer versions and updates by implementing new code if certain parameters or tables are missing.
• Updates to this job include ensuring all necessary procedures and tables are present and that the job can handle changes in data schema or additional requirements like tracking more users.

By systematically checking and reporting on orphaned database users, this job helps maintain robust security measures in SQL Server environments, ensuring that only authorized users have access to databases.