SQL Server MSDB Databases, Agent Proxies and Public Roles

SQL Server MSDB Databases, Agent Proxies and Public Roles

4 Minutes Read Updated on May 21, 2025

What are SQL Server msdb databases, agent proxies 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.  It is used by SQL Server Agent services for scheduling alerts and jobs and by other features such as SQL Server Management Studio, Service Broker and Database Mail.

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

Physical Properties of msdb

The table below presents the initial configuration values for the msdb data and log files. Note that the sizes of these files can vary slightly depending on the edition of the SQL Server Database Engine.

File Logical name Physical name File growth
Primary data MSDBData MSDBData.mdf Autogrow by 10 percent until the disk is full.
Log MSDBLog MSDBLog.ldf Autogrow by 10 percent to a maximum of 2 terabytes.

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.

To help protect the information that is stored in msdb, it’s recommended to place the msdb transaction log on fault tolerant storage.

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.

USE [msdb]

GO

SELECT sp.name AS proxyname

FROM dbo.sysproxylogin spl

JOIN sys.database_principals dp

ON dp.sid = spl.sid

JOIN sysproxies sp

ON sp.proxy_id = spl.proxy_id WHERE principal_id = USER_ID(‘public’);

GO

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.
USE [msdb]

GO

EXEC dbo.sp_revoke_login_from_proxy @name = N’public’, @proxy_name =

N'<proxyname>’;

GO

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 that 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 that 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!

Ben Balkin
Ben Balkin is a professional writer and blogger specializing in technology and innovation. As a contributor to the Calcom blog, Ben shares practical insights, useful tips, and engaging articles designed to simplify complex processes and make advanced technological solutions accessible to everyone. His writing style is clear, insightful, and inspiring, reflecting his strong belief in technology's power to enhance quality of life and empower businesses.

Related Articles

Server Hardening Tools

Server Hardening Tools

April 7, 2022

Defining and implementing a comprehensive server security policy is an essential step in the process…

Changes from PCI DSS Version 3.2.1 to 4.0

Changes from PCI DSS Version 3.2.1 to 4.0

June 20, 2022

In March 2022, PCI DSS launched a 4.0 version, which sets the operational and security…

Ryuk attack- protect your organization

Ryuk attack- protect your organization

June 5, 2019

Ryuk ransomware was first detected in August 2018. One of its famous attacks happened on…

Ready to simplify compliance?

See automated compliance in action—book your demo today!

Share this article