Cross DB Ownership Chaining

Job details

Name:	Cross DB Ownership Chaining
Platform:	Sqlserver
Category:	Security
Premium package:	Security and compliance package
Description:	Checks cross-database ownership chaining across all databases at the instance level.
Long description:	Checks cross-database ownership chaining across all databases at the instance level.
Version:	1.2
Default schedule:	7 1 1 *
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
return status	1	Return status value (ALARM – 2, WARNING – 1, or OK – 0) when the “cross db ownership chaining” parameter is enabled.
disable cross db ownership chaining	NO	If set to “YES“ the alert will disable “cross db ownership chaining” (if it is enabled) by running sp_configure stored procedure.
history threshold	365	The maximum number of days to keep statistics for in the historic tables.

Job Summary

Purpose: The purpose of this job is to monitor and manage the configuration of cross-database ownership chaining across all databases at the instance level of a SQL Server.
Why: This job is crucial because when the cross-database ownership chaining is enabled, it allows a member of the db_owner role in a database to access objects owned by a login in any other database, which could lead to unnecessary and potentially harmful information disclosures.
Manual checking: You can check this manually in the database by issuing the following SQL commands:

SELECT value_in_use FROM master.sys.configurations WHERE name = 'cross db ownership chaining'

Implementation Details

The monitoring job periodically checks the status of cross-database ownership chaining and records it.
If the chaining is found to be enabled, the job can also disable it based on pre-set parameters, helping in adhering to security compliance practices.
The job maintains historical logs to track changes over time, which assists in auditing and compliance tracking.

Overview of Tables and Procedure

Tables Created:
- “dbw_cross_db_own_chaining_status_info”: Stores the current value and date of checking.
- “dbw_cross_db_own_chaining_status_histr”: Maintains a history of values to track changes over time.

Procedure:
- “dbw_cross_db_own_chaining_proc”: This stored procedure is responsible for managing and logging the status of ownership chaining, including enabling or disabling it based on the job’s configuration.

Job Dependencies

This job has several dependencies that are critical for its operation, including:
- Main procedure (handle overall control flow and logic)
- Current status table (stores the immediate status fetched from the server)
- History table (aggregates and maintains status over a stipulated period)

Presentation and Reporting

Current Configuration: This report showcases the current setting of the cross-db ownership chaining parameter.
Last Day Configuration History: Graphical representation of parameter value changes over the last day.
Aggregate Statistics: Visual statistics of parameter checks, displayed over each day, maintain an aggregate of check counts based on the history threshold settings.

Upgrade and Installation

The job includes an upgrade script which adjusts the stored procedures in line with the advancements or modifications in the product version.
The forced installation setting ensures the job is installed regardless of earlier versions existing, which is crucial for maintaining the latest monitoring mechanisms.

Applicability

This monitoring job is compatible with instances running SQL Server 2005 and later, including Azure SQL Managed Instances, ensuring broad applicability across different environments.

In sum, the “Cross DB Ownership Chaining” monitoring job in dbWatch Control Center is vital for maintaining database security by providing oversight over potentially risky database configurations, thereby helping organizations enforce best practices in database security and compliance.