SQL Server – Role to View but Not Create SQL Agent Jobs

sql-server-agent

The Microsoft SQL Server database role SQLAgentReaderRole apparently lets a member create SQL Agent jobs along with viewing jobs, job steps, and results.

We need to allow a user to see all SQL agent jobs, steps, schedules, and results, but not create any jobs. This can almost be provided using several different views, but the results are messy and hard to read — I want to be able to see the SQL Agent node in Management Studio and verify things.

Is there any role or permission that would allow this? Let's assume SQL 2008 through SQL 2016. Thanks.

Best Answer

Seems like this should work:

The "reader" name is definitely misleading. This may be stating the obvious, but to get around this I've granted the SQLAgentReaderRole role and explicitly denied EXECUTE on add job sprocs in msdb for the logins with the role.
DENY EXECUTE ON [dbo].[sp_add_job] TO [YourLogin] GO 
DENY EXECUTE ON [dbo].[sp_add_jobschedule] TO [YourLogin] GO 
DENY EXECUTE ON [dbo].[sp_add_jobserver] TO [YourLogin] GO 
DENY EXECUTE ON [dbo].[sp_add_jobstep] TO [YourLogin] GO 
DENY EXECUTE ON [dbo].[sp_add_jobstep_internal] TO [YourLogin] GO

Related Solutions

SQL Server Agent Missing in SSMS – How to Find It

If you are not granted the needed permissions, you will not be able to see the Agent, no matter whether you run Enterprise/Standard/Datacenter, etc...

The roles needed are sysadmin, or the individual roles here - http://msdn.microsoft.com/en-us/library/ms188283.aspx

Execute Permission Denied on Object sp_start_job in SQL Server

I don't like the TRUSTWORTHY option because it significantly increases your exposure to a variety of things. As Remus explains in this answer, it essentially elevates any db_owner to sysadmin. Some other things worth reading are a series on TRUSTWORTHY by Sebastian Meine, the BOL topic, and a KB article (even though the assembly portions may not be relevant to you in this scenario):

(And there are tons of other posts out there cautioning against the blind use of this property - just because it works and it is easy doesn't mean it's the right thing to do - in fact that should make you question it even more.) So I would suggest a different approach (and there are still others, such as signing with a certificate, but this has always worked for me):

Create the procedure in msdb.

Create a user for this user's login in msdb:

USE msdb;
GO
CREATE USER floobarama FROM LOGIN floobarama;

Grant the user execute privileges on the stored procedure:
```
GRANT EXECUTE ON [dbo].[RunJob] TO floobarama;
```

Test it - either by calling the procedure from another database:

USE tempdb;
GO
EXECUTE AS LOGIN = N'floobarama';
GO
EXEC msdb.dbo.RunJob @job_name = N'whatever';
GO
REVERT;

Or an easier test, in case you don't want to run any job now and don't want to wait until this user executes it to find out whether they have sufficient access to msdb:

USE msdb;
GO
CREATE PROCEDURE dbo.whatever
WITH EXECUTE AS N'sysadminaccount'
AS
BEGIN
  SET NOCOUNT ON;
  SELECT [I am really...] = SUSER_SNAME();
END
GO
GRANT EXECUTE ON dbo.whatever TO floobarama;

USE tempdb;
GO
EXECUTE AS LOGIN = N'floobarama';
GO
EXEC msdb.dbo.whatever;
GO
REVERT;

Result should be:

I am really...
---------------
sysadminaccount

Validate that this doesn't expose anything else in msdb to this user:
```
USE tempdb;
GO
EXECUTE AS LOGIN = N'floobarama';
GO
SELECT job_id FROM msdb.dbo.sysjobs;
GO
REVERT;
```
Result should be...

Msg 229, Level 14, State 5, Line 21
The SELECT permission was denied on the object 'sysjobs', database 'msdb', schema 'dbo'.

...since creating a user in a database doesn't give them automatic rights to anything in that database; you need to explicitly do so either for that user, or for a role or group they are in (including public).

Best Answer

Related Solutions

SQL Server Agent Missing in SSMS – How to Find It

Execute Permission Denied on Object sp_start_job in SQL Server

Related Question