Logins default database check
Job details
| Name: | Logins default database check | |
| Platform: | Sqlserver | |
| Category: | Security | |
| Description: | This procedure performs two checks. ‘Sysadmin Logins Default Database Check’ which | |
| Long description: | This procedure performs two checks. ‘Sysadmin Logins Default Database Check’ which | |
| Version: | 1.2 | |
| Default schedule: | 30 7 7 * | |
| Requires engine install: | Yes | |
| Compatibility tag: | .[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2000′ & hasengine=‘YES’ & (engine_edition = ‘Microsoft SQL Server’ | engine_edition = ‘Azure SQL Managed Instance’)] |
Parameters
| Name | Default value | Description |
|---|---|---|
| ignore databases | model | List of databases (separated by comma) which will be ignored. You can use % (percent sign) to represent wild card characters. |
| return status for sysadmins | 0 | Return status value (ALARM – 2, WARNING – 1, or OK – 0) when logins with sysadmin privileges have default database other than master. |
| ignore logins | List of logins (separated by comma) which will be ignored. You can use % (percent sign) to represent wild card characters. | |
| return status for default db | 1 | Return status value (ALARM – 2, WARNING – 1, or OK – 0) when logins with a default database that is either not online or does not exist. |
Job Summary
The purpose of this monitoring job is to ensure security and proper configuration of database logins for systems using Microsoft SQL Server, including SQL Server instances hosted on Azure Managed Instances. Specifically, it addresses two critical checks:
- Sysadmin Logins Default Database Check: This check ensures all logins with sysadmin privileges have their default database set to ‘master’. This is a critical security practice as it restricts sysadmin logins to a default environment, minimizing unauthorized access to other databases.
- Default Database Validity Check: This verifies whether the default databases assigned to all logins are online and exist. It prevents issues related to users logging into non-existent or offline databases, which can lead to errors and connectivity problems.
Why this job is important:
- Ensures security protocols are adhered to, particularly concerning sysadmin accounts.
- Improves system reliability by verifying that all default databases are accessible and existing, preventing potential login issues.
Method of Check
This monitoring job is automated through a stored procedure within SQL Server called “dbw_logins_default_database_proc.” It dynamically checks the current logins against the security and configuration standards mentioned.
Manual verification: Although the procedure automates checks, manual verification (when required) involves inspecting specific SQL Server system tables and settings. Example SQL commands for a manual check include:
SELECT name, type_desc, default_database_name, modify_date FROM sys.server_principals WHERE type IN('U','G','S')Implementation Details
The procedure executes within the SQL Server environment and involves:
- Creation of temporary tables to manage ignored logins and databases as specified in the parameters.
- Cursors used to iterate over existing logins not specified in the ignore lists.
- Condition checks to identify and report deviations from specified standards, such as incorrect default databases or unauthorized default database settings for sysadmin logins.
Reporting and Output
The results of the monitoring are formatted into a report providing detailed information on each login’s default database setting, including the modification date. This allows database administrators to quickly see configurations that may need attention.
Dependencies and Setup
- Dependencies: The implementation of this job relies on existing SQL Server system views and the creation of temporary tables within the procedure.
- Scheduled Runs: By default, the procedure is set to run periodically at specified intervals, ensuring ongoing compliance and early detection of misconfigurations.
- The job also includes exception handling to capture any errors during the execution of the monitoring process, ensuring that even in the case of failure, appropriate notifications and logging occur.
Versioning and Upgrades
- Version Information: This monitoring job is currently at version 1.2, ensuring that it includes the latest checks and follows best practices as recommended by dbwatch.com.
- Upgrade Path: There are provisions for upgrading the procedure from previous versions by altering the existing procedure code, ensuring enhanced functionality and maintaining compatibility with newer SQL Server features or requirements.
This job encapsulates a mix of security, compliance, and operational best practices tightly integrated into a scheduled SQL Server monitoring process. It reflects a proactive approach to database administration, aimed at preventing rather than reacting to issues post-factum.