A new user (associated with an existing login) is created in msdb and assigned membership:

use [msdb];
create user [TheUser] for login [AnExistingLogin];
alter role [SQLAgentUserRole] add member [TheUser];
alter role [SQLAgentReaderRole] add member [TheUser];

-- Impersonate the user via an SP in another DB with execute as...
select top 10 * from msdb.dbo.sysjobs; -- fail

The SELECT permission was denied on the object 'sysjobs', database 'msdb', schema 'dbo'.

Try the obvious:

grant select on msdb.dbo.sysjobs to [TheUser]

select top 10 * from msdb.dbo.sysjobs; -- success

Isn't the proper way to work within the SQLAgent* roles?

SO question with competing answers

Blog on SQLAgentReaderRole

  • I remember trying to deal with this a long time back, and I remember trying to mess with proxies and trustworthy databases and all kinds of hacks and eventually threw my hands in the air and created stored procedures - with execute as owner - and granted the user explicit rights to those. – Aaron Bertrand Aug 23 '14 at 12:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.