Minimum privileges to read SQL Jobs using SQL SMO

Question

I wrote an application to use SQL SMO to find all SQL Servers, databases, jobs and job outcomes. This application is executed through a scheduled task using a local service account. This service account is local to the application server only and is not present in any SQL Server to be inspected. I am having problems getting information on job and job outcomes when connecting to the servers using a user with dbReader rights on system tables. If we set the user to be sysadmin on the server it all works fine.

My question to you is: What are the minimum privileges a local SQL Server user needs to have in order to connect to the server and inspect jobs/job outcomes using the SQL SMO API?

I connect to each SQL Server by doing the following:

var conn = new ServerConnection
      {
       LoginSecure = false,
       ApplicationName = "SQL Inspector",
       ServerInstance = serverInstanceName,
       ConnectAsUser = false,
       Login = user,
       Password = password
      };
var smoServer = new Server (conn);

I read the jobs by reading smoServer.JobServer.Jobs and read the JobSteps property on each of these jobs.

The variable server is of type Microsoft.SqlServer.Management.Smo.Server. user/password are of the user found in each SQL Server to be inspected.

If "user" is SysAdmin on the SQL Server to be inspected all works ok, as well as if we set ConnectAsUser to true and execute the scheduled task using my own credentials, which grants me SysAdmin privileges on SQL Server per my Active Directory membership.

Thanks!

Answer 1

Just been through this myself. From SQL Books Online, here's what I found out...

To get or set Job object properties, users must be a member of the SQLAgentUserRole database role on the MSDB database, or be the owner of the job, or be a member of the sysadmin fixed server role.

To create a job, users must be a member of the SQLAgentUserRole, SQLAgentReaderRole, and SQLAgentOperatorRole database roles on the MSDB database, or be a member of the sysadmin fixed server role.

To drop a job, users must be a member of the SQLAgentUserRole database role on the MSDB database, or be the owner of the job, or be a member of the sysadmin fixed server role.

Cheers,

John.