How to grant permission to use the SQL Server Agent?
This post is part of a series I have called “An Introduction to SQL Server.” The material is for people who new to SQL Server and need to get up to speed. I got asked this question while delivering a four day Introduction to SQL Server class recently and thought it was worth taking the time to write blog about.
The question was, “How can I give someone permission to work with SQL Server Agent? Without giving them sysadmin permissions?” Good question.
The SQL Server Agent is a vital component for scheduling and automating tasks in SQL Server. However, granting users permission to use the SQL Server Agent can often seem like it requires sysadmin privileges. This is a common misconception. You can provide necessary permissions using predefined SQL Server Agent roles within the msdb database without giving users full sysadmin access.
In this blog post, I will guide you through the process of granting users the appropriate permissions to use SQL Server Agent by leveraging the SQL Agent roles in the msdb database.
Understanding SQL Server Agent Roles
SQL Server provides three fixed database roles in the msdb database specifically for managing SQL Server Agent:
- SQLAgentUserRole
- SQLAgentReaderRole
- SQLAgentOperatorRole
SQLAgentUserRole
- Purpose: Members of this role can create their own jobs, view the list of jobs, and manage their own jobs.
- Best for: Users who need to create and manage their own jobs without accessing jobs owned by others.
SQLAgentReaderRole
- Purpose: Members of this role can view the list of all jobs, including the ones they don’t own, and read job-related data.
- Best for: Users who need to monitor jobs and view job histories, but don’t need to manage jobs owned by others.
SQLAgentOperatorRole
- Purpose: Members of this role can manage all SQL Server Agent jobs, including creating, modifying, and deleting jobs, and starting and stopping jobs owned by others.
- Best for: Users who need comprehensive control over SQL Server Agent jobs, such as DBAs or power users.
Granting Permissions Using SQL Agent Roles
Let’s walk through the steps to assign a user to one of these roles. Assume you have a user Jan who needs to manage her own jobs. Jan has read permission on the helpdesk database, when she logs in using a SQL Server Login her Object Explorer looks like this. As you can see there, the SQL Server Agent is not available in Object Explorer.
Step 1: Connect to SQL Server and Open SQL Server Management Studio (SSMS)
Ensure you have the necessary administrative permissions to modify roles within the msdb database.
Step 2: Add User to msdb Database
Before assigning a role, you need to add the user to the msdb database.
USE msdb;
GO
CREATE USER [Jan] FOR LOGIN [Jan];
GO
Step 3: Assign the Appropriate Role
For this example, we will add Jan to the SQLAgentUserRole.
USE msdb;
GO
EXEC sp_addrolemember 'SQLAgentUserRole', 'Jan';
GO
Verification
When Jan logs in again you can see that she can now see the SQL Server Agent and she can create a job
To verify that Jan has the appropriate permissions, you can check the members of the SQLAgentUserRole.
USE msdb;
GO
SELECT
roleprin.name AS RoleName,
memprin.name AS MemberName
FROM
sys.database_role_members AS members
JOIN sys.database_principals AS roleprin ON members.role_principal_id = roleprin.principal_id
JOIN sys.database_principals AS memprin ON members.member_principal_id = memprin.principal_id
WHERE
roleprin.name = 'SQLAgentUserRole';
GO
This query will list all members of the SQLAgentUserRole, and you should see Jan included in the results.
Conclusion
Granting users the necessary permissions to use SQL Server Agent does not require sysadmin privileges. By using the fixed database roles in the msdb database (SQLAgentUserRole, SQLAgentReaderRole, and SQLAgentOperatorRole), you can fine-tune the level of access and control users have over SQL Server Agent jobs. This approach enhances security by following the principle of least privilege while still providing the necessary functionality for users to manage SQL Server Agent jobs.
Following the steps outlined in this blog, you can efficiently manage user permissions for SQL Server Agent, ensuring a secure and well-administered SQL Server environment.
Ready to Optimise Your SQL Server Performance?
If you found this guide helpful, imagine what a full SQL Server Health Check could do for your organisation! Our team of experts will analyze your SQL Server setup, identify potential issues, and provide actionable recommendations to enhance performance and security.
Don’t miss out on this opportunity to ensure your SQL Server is running at its best. Sign up for your FREE SQL Server Health Check today and take the first step towards optimizing your database environment.
Stay tuned for more insights and tips in our “Introduction to SQL Server” series, and make sure to share this post with your colleagues who could benefit from it!
0 Comments