The Public Role in the MSDB Database, No Proxies Allowed

What are SQL agent proxies, msdb databases and the public roles

SQL Agent proxies are a form of built-in service that allows the schedule and running of automated tasks within SQL Server. These tasks can perform various actions related to database management.

The msdb database is a crucial system database in Microsoft SQL Server which primarily serves SQL server agents. These databases store information related to SQL Agent jobs, including their configuration, execution history, vital system tables and data. They also maintain a comprehensive backup-and-restore history of the msdb database.

Unlike normal proxies which use specific user credentials and can therefore be given limited permissions to do a specific task, such as restricting access to specific databases, SQL server agents do not have such individual restrictions.

The public role of an SQL server is assigned to all users  by default. The public role typically has very limited permissions or no permissions at all. This ensures users need to be explicitly assigned to a specific role with appropriate permissions to do anything meaningful within a database.

SQL Server Orphaned Users – Detection and Remediation Steps

Why it’s important to not give an SQL agent proxy permissions in the public role

Ensuring SQL agent proxies are not granted access in the public role provides a level of assurance that only authorized users can control the automated tasks and tools within an SQL Server. Granting access to SQL Agent proxies for the public role would enable all users to utilize the proxy, even those with lower permissions, enabling them to potentially gain access to information they aren't supposed to.

Not granting public role permissions prevents potential security risks and maintains the smooth operation of your automated processes.

Impact of revoking the public role permissions from a proxy

It is important to consider the needs of the proxy when revoking the public role as, if no alternative user defined database roles are granted, the proxy could lose access to data needed to properly function.

How to determine if access to proxies has been granted to the msdb's public role

Use the following syntax to determine if access to any proxies have been granted to the msdb database’s public role.

This query should not return any rows.

How to Ensure the public role in the msdb database is not granted access to SQL Agent proxies

1. Ensure the required security principals are explicitly granted access to the proxy

(use sp_grant_login_to_proxy).

1. Revoke access to the <proxyname> from the public role.

Default value

By default, the msdb public database role does not have access to any proxy.

Best practices

This setting is just one of many which must be properly configured for the smooth running and operations of a server. Each setting must balance with all the other settings, creating a delicate ecosystem which must be cared for and maintained.

Server hardening helps make this process easier, acting  like a multi-layered shield against potential security threats. Server hardening provides a comprehensive security approach that goes beyond just configuring SQL Server permissions. It fortifies the entire server environment, making it a more robust and secure platform for hosting your valuable SQL Server data. Find out more HERE!