Check database and server principal mapping

Job details

Parameters

Job Summary

• Purpose:
  The purpose of this monitoring job is to ensure that database principals, particularly database owners (dbo), are correctly mapped to server logins in Microsoft SQL Server and Azure SQL Managed Instances. This job targets instances that are SQL Server 2005 and later, emphasizing the importance of correct mapping to prevent potential issues, such as error logs rapidly increasing due to errors like SQL Server Error 15517.

• Why:
  This job is critical to maintain database security and integrity by:
  • Verifying that all database users, specifically dbo account, have an associated server login.
  • Providing the ability to correct mappings where necessary, by altering database authorization when enabled.
  • The maintenance of mapping is crucial in environments with stringent security requirements and operational procedures.

• Manual checking:
  To manually check for dbo users without server login mapping, the following SQL query can be run:

select db_name "Database", user_name "User", create_date "Created", default_schema "Default schema", case when server_login is null then ' ' else server_login end "Server login", case when login_type is null then ' ' else login_type end "Type" from dbw_principal_stat where user_name = 'dbo' and server_login is null order by 1,2 asc;

Technical Overview

Implementation Details

The job uses multiple SQL queries and procedures to monitor and possibly alter database principals to ensure proper security configuration. These include:

• Creating and populating a table `dbw_principal_stat`, for storing principal statistics across all non-system databases.
• A stored procedure `dbw_proc_db_principal`, that handles the detailed checking and any alterations based on job parameters.

Job Parameters and Conditions

• The parameters such as ‘enable altering user’, ‘Server Login’, and ‘return status’ drive the behavior of this job:
  • These parameters decide whether the job should automatically fix missing mappings and determine the behavior if owners are not mapped.

Dependencies

• Relies on ‘dbw_principal_stat’ for information storage and ‘dbw_proc_db_principal’ for logic execution. The proper functioning of these objects is necessary for the job.

Reports and Outputs

The job produces a detailed report concerning two main scenarios:

• A list of all dbo users across databases that do not have server login mappings.
• A comprehensive mapping of all database owners within the server environment showing their associated server login information, if any.

The job’s outcome includes detailed statistical output concerning:

• Total number of users checked,
• The ones with no server logins, and specifically,
• How many such cases involve database owners (dbo).

Upgrade Paths

The procedures undergo potential changes in behavior or logic through updates detailed in ‘upgrade’ sections of the task configuration, ensuring future improvements and bug fixes are adequately handled.

System Impact and Execution

• Scheduled to run monthly by default at 3:15 AM on the first day of each month, which can be adjusted according to the specific environmental needs for maintaining minimal impact during peak hours.
• Configurations like always-on check and handling of ReadOnly databases are particularly notable for their adaptability in diverse SQL Server environments.

Conclusion

This dbWatch Control-Center monitoring job is an essential part of maintaining the robustness and reliability of database operations, specifically concerning the pivotal role of database owners within SQL Server and Azure Managed Instance environments. The ability to auto-correct these mappings if enabled helps in minimizing manual interventions and prevents potential service disruptions associated with misconfigurations.