Check database and server principal mapping


Job details

Name: Check database and server principal mapping
Platform: Sqlserver
Category: Maintenance
Premium package: Maintenance package
Description: Checks if the database owner (dbo) is mapped into any Server Login (server principal).
Long description: The Alert also collects statistics for all database users (in all none system databases) including Server login information. Users with principal_id 2, 3 and 4 will not be checked (guest, INFORMATION_SCHEMA and sys).
Version: 2
Default schedule: 0 * * *
Requires engine install: Yes
Compatibility tag: .[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2005′ & hasengine=‘YES’ & eng_inst_priv = 0 & (engine_edition = ‘Microsoft SQL Server’ engine_edition = ‘Azure SQL Managed Instance’)]

Parameters

Name Default value Description
enable altering user NO This parameter enables the task to map database user to a Server Login defined by the “Server Login” parameter value. Possible values: YES and NO
Server Login sa Name of the Server Login which will be mapped with all database users (defined by the “database principals to map” parameter value) which are not mapped to any Server Login.
return status 0 Return status value (ALARM – 2, WARNING – 1, or OK – 0) when database owner is not mapped into any Server Login.

Job Summary

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:

Job Parameters and Conditions

Dependencies

Reports and Outputs

The job produces a detailed report concerning two main scenarios:

The job’s outcome includes detailed statistical output concerning:

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

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.