Windows BUILTIN groups
Job details
| Name: | Windows BUILTIN groups | |
| Platform: | Sqlserver | |
| Category: | Security | |
| Premium package: | Security and compliance package | |
| Description: | Checks that Windows BUILTIN groups are not SQL Logins. | |
| Long description: | Checks that Windows BUILTIN groups are not SQL Logins. These groups should not be used for any level of access into a SQL Server Database Engine instance. | |
| Version: | 1.2 | |
| Default schedule: | 32 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 Windows BUILTIN groups is a SQL Login. |
| disable login | NO | If set to “YES“ the alert will disable the login by running “ALTER LOGIN [login_name] DISABLE“. |
| 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 ensure that Windows BUILTIN groups are not set up as SQL Logins in SQL Server instances. These groups are default system accounts that should not have direct access to SQL Server database functionalities due to security best practices.
- Why: This monitoring job is crucial to maintaining the security integrity of SQL Server databases by preventing unauthorized system-level access by built-in operating system user groups. If these groups are enabled as SQL Logins, it could potentially lead to unauthorized access or manipulations of database functionalities.
- Manual checking: To manually check if Windows BUILTIN groups are configured as SQL Logins, use the following SQL commands:
SELECT pr.[name], pe.[permission_name], pe.[state_desc]
FROM sys.server_principals pr
JOIN sys.server_permissions pe
ON pr.principal_id = pe.grantee_principal_id
WHERE pr.name like 'BUILTIN%'Job Implementation Details
- This job includes several SQL operations organized into creating tables, procedures, and running checks on database objects. These operations include:
- Creation of history tracking tables and indexes to manage data regarding BUILTIN groups’ permissions over time.
Job Dependencies
The job includes dependencies on certain database objects that are crucial for the execution of the job:
- “dbw_windows_builtin_groups_proc”: Main procedure that manages the control logic for handling Windows BUILTIN groups as SQL Logins.
- “dbw_windows_builtin_groups_info”: A table that temporarily holds the current status of the permissions of BUILTIN groups.
- “dbw_windows_builtin_groups_histr”: Historic data table that aggregates data over time to provide a persistent view of changes and the current status.
Scheduled and Triggers
The job operates on a default schedule which is set to execute once per day, ensuring daily checks on the Windows BUILTIN groups configurations for any unauthorized changes or configurations.
Security and Compliance
- This task is related to the Access Control compliance aspect, ensuring that only appropriate users and groups have access into SQL Server environments. It tightly aligns with best security practices by regularly verifying that critical, non-user groups like Windows BUILTIN do not have SQL Server access privileges.
Presentation and Reporting
- Reports generated by this job provide insights into BUILTIN groups created as SQL Logins, including specific permissions granted and the state of those permissions.
- Data presentation includes:
- A tabular representation highlighting the name and permissions of SQL Logins associated with BUILTIN groups.
By maintaining these monitoring and constraint checks, the job plays an integral role in safeguarding against possible security risks associated with improper privilege grants to critical system-level groups.