Minimum privileges to read SQL Jobs using SQL SMO
- by Gustavo Cavalcanti
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!
