Public server role

Job details

Name:	Public server role
Platform:	Sqlserver
Category:	Security
Premium package:	Security and compliance package
Description:	Checks if extra permissions have been granted to the public server role.
Long description:	Checks if extra permissions have been granted to the public server role. Every SQL Server login belongs to the public role and cannot be removed from this role.
Version:	1.2
Default schedule:	22 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 extra permissions have been granted to the public server role.
revoke extra permission	NO	If set to “YES“ the alert will revoke extra permission from the public server role by running “REVOKE <permission_name> FROM public”.
history threshold	365	The maximum number of days to keep statistics for in the historic tables.

Job Summary

Purpose: The purpose of this monitoring job is to ensure that no unintended or excessive permissions have been granted to the “public” server role in SQL Server, maintaining database security and compliance.
Why: It’s crucial because every SQL Server login is part of the public role by default, and the permissions granted to this role affect all logins. Misconfigurations can lead to unauthorized data access or manipulation.
Manual checking: For manual verification of permissions for the public server role, the following SQL command can be issued:

SELECT * FROM master.sys.server_permissions WHERE grantee_principal_id = SUSER_SID(N'public') and state_desc LIKE 'GRANT%';

Core Functionality

It checks if extra permissions have been granted to the “public” server role and optionally revokes them.
Records and maintains a history of granted permissions to monitor changes over time.

Implementation Details

“dbw_public_server_role_proc“ is a stored procedure that checks for extra permissions and updates tables where details are log.
Two tables, “dbw_public_server_role_info“ and “dbw_public_server_role_histr“, are used respectively to log current permissions and maintain history.
Allows revocation of extra permissions if specified.

Parameters Overview

“return status”: Determines the status level (OK, WARNING, ALARM) based on the check’s results.
“revoke extra permission”: Option to automatically revoke detected extra permissions.
“history threshold”: Sets how long the history of permissions is maintained.

Tables and Dependency Management

The job has dependencies on the aforementioned stored procedure and tables which ensure that current and historical data is reliably managed and maintained.
Dependencies are reset if any failure occurs during execution, guaranteeing system consistency.

Security and Compliance

Has built-in capabilities to actively update the security compliance framework, reflecting the current security status after each execution.

Scheduled Execution

Set to periodically execute at defined intervals ensuring constant monitoring and immediate action on any configuration drift relating to server roles permissions.

Job Outputs

Permission Name	State
permission1	Granted
permission2	Granted

Historical Representation

Permissions that have been flagged and actioned upon are also presented in an aggregated manner, providing an easy view of all permissions changes over specified periods.

System Requirements

The job is compatible with Microsoft SQL Server environments, specifically designed to operate where comprehensive server-level permission monitoring is crucial.

In essence, this dbWatch job automatically audits and manages server role permissions in SQL Server environments, providing both active management through revocation options and historical tracking for compliance and auditing purposes.