Public role (msdb)


Job details

Name: Public role (msdb)
Platform: Sqlserver
Category: Security
Premium package: Security and compliance package
Description: Checks that the public role in the msdb database is not granted access to SQL Agent proxies.
Long description: Checks that the public role in the msdb database is not granted access to SQL Agent proxies. SQL Agent proxies define a security context in which a job step can run.
Version: 1.2
Default schedule: 16 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 public role in the msdb database is granted access to SQL Agent proxies.
revoke public login from proxy 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

SELECT sp.name FROM msdb.dbo.sysproxylogin spl
JOIN msdb.sys.database_principals dp
ON dp.sid = spl.sid
JOIN msdb.dbo.sysproxies sp
ON sp.proxy_id = spl.proxy_id
WHERE principal_id = USER_ID('public')

Implementation Details

Presentation in dbWatch

Task Name: msdb public role
Description: The table shows SQL Agent proxies that the public role in the msdb database is granted access to.
__. Proxy Name: (Name of the proxy)
__. Date Discovered: (Date when the access was recorded)

Monitoring Job Features

Compliance and Upgrades

By employing this dbWatch job, organizations can significantly strengthen their SQL Server database security posture by continually monitoring and controlling access to critical SQL Agent functionalities.