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 Answer 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.