What should be the command for following role creation requirement?

Question

I need to create a role where the assigned user can access only the 'AdventureWorksDB" where he will have execute permission to all tables,views and stored procedures in the database. I wrote the below script.

USE AdventureWorks2012;
GO
CREATE USER test1 FOR LOGIN test1;
GO 

CREATE ROLE db_executor

GRANT EXECUTE TO db_executor

EXEC sp_addrolemember 'db_executor', 'test1'

But he also needs SQLAgentOperatorRole permission for SQL Agent. But If I am to grant this I have to give him the access to msdb to be able to do this. But if I give the user , access to msdb user can access other databases as well. What could I do to make the user access only one database ad have full access to SQL Agent?

score 2 · Accepted Answer · edited Apr 13 '17 at 12:42

I am not sure of any other alternative but i believe user needs to be there in MSDB or should have the admin rights to execute or perform any required action on the job. There is a connect item about this as well, SQL2005 SQLAgent MSDB security roles

Or you can use an option as mentioned here:

You could create a stored procedure that runs the job. You can use with execute as owner to run the stored procedure as the database owner. That way, the users themselves don't need permissons on sp_start_job

Also, if feasible, you can read Launching SQL Agent Job from stored procedure not on [msdb] database, or, How Necessary is SET TRUSTWORTHY ON?

What should be the command for following role creation requirement?

1 Answers1