1

I'm currently writing a database management application that connects to all of my databases and returns metadata for each database. The user currently has db_datareader role for MSDB but the DataReader .NET object only contains schema and no rows after executing a query on sysjobs or sp_help_jobs stored procedure in the code or in SSMS. I've tried granting execute and select on the database and object levels to no avail. When I give SA to the user it is able to return data, but I do not want to give the user SA.

What is the specific permission needed to retrieve the data from system tables and maintain the highest level of security?

1

Try making your user account a member of the msdb database's SQLAgentReaderRole.

The rights granted by that role membership are described here:

https://msdn.microsoft.com/en-us/library/ms188283.aspx

Look at the description of rights under SQLAgentReaderRole Permissions. You will see that it has rights to read the information on the jobs. This does not require being a member of the db_datareader role. It does allow the user to enumerate jobs, view properties, and view job history.

The user account can only make changes to a job if that account is the owner of the SQL Agent job. Otherwise, it is read-only.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.