Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have create a user and grant many permissions and roles to it including SQLAgentUserRole and SQLAgentReaderRole. My application check the status of agent if it is running or not and whenever I am trying to run my application using user I have created it is not able to identify a Running SQL Server Agent.

When I do same using a user with sysadmin it works fine.

What minimum permissions I need to provide to a user so that it can check the status of SQL Server Agent Service.

share|improve this question
    
How does your application check the status of agent? Are you using the approach posted by Aaron Bertrand at stackoverflow.com/questions/11632849/… ? If so, what error message do you get? –  RLF Apr 1 at 12:45
    
I believe what causing issue is.. this statement SELECT * FROM MASTER.dbo.sysprocesses I got only one row when running it using my user. –  Ashish Apr 1 at 13:04
    
Yes. First of all dbo.sysprocesses is not the best place to look. But it is true that a sysadmin sees all processes and others see only their own process. If you have "VIEW SERVER STATE" permission you can see more details. However, I guess that I am unclear on what you looking for: (1) whether a particular job is running or (2) whether SQL Agent is active and runnable. –  RLF Apr 1 at 15:15
    
@RLF apologies I messed it up.. Yes we are using "master..xp_servicecontrol" to check status of SQL Agent Service and I already provided user with GRANT EXECUTE ON SYS.XP_PROP_OLEDB_PROVIDER TO User; Still it is not working. Any help? –  Ashish Apr 2 at 5:51
add comment

2 Answers 2

To set the Service Startup Account for SQL Server Agent
In Registered Servers, click the plus sign to expand Database Engine.

  • Click the plus sign to expand the Local Server Groups folder

.

  • Right-click the server instance where you want set up the Service Startup Account, and select SQL Server Configuration Manager….


  • In the User Account Control dialog box, click Yes.


  • In SQL Server Configuration Manager, in the console pane, select SQL Server Services.
    In the details pane, right-click SQL Server Agent (server_name), where server_name is the name of the SQL Server Agent instance for which you want to change the service startup account, and select Properties.
  • In the SQL Server Agent (server_name) Properties dialog box, in the Log On tab, select one of the following options under Log on as:


  • Built-in account: select this option if your jobs require resources from the local server only. For information about how to choose a Windows built-in account type, see Selecting an Account for SQL Server Agent Service.


  • Important note Important


  • The SQL Server Agent service does not support the Local Service account in SQL Server Management Studio

.

  • This account: select this option if your jobs require resources across the network, including application resources; if you want to forward events to other Windows application logs; or if you want to notify operators through e-mail or pagers. If you select this option:


  • In the Account Name box, enter the account that will be used to run SQL Server Agent. Alternately, click Browse to open the Select User or Group dialog box and select the account to use.


  • In the Password box, enter the password for the account. Re-enter the password in the Confirm password box.


Click OK.
In SQL Server Configuration Manager, click the Close button.

share|improve this answer
    
I don't think @Ashish was looking for how to configure SQL Server Agent, but wanted to know if it (1) was running or (2) was running a particular job. –  RLF Apr 1 at 20:59
add comment

When I run as a sysadmin on my server, this runs fine.

exec master.dbo.xp_servicecontrol 'QUERYSTATE', 'SQLServerAgent'

If I run it with a non-sysadmin account, I get the following message:

The EXECUTE permission was denied on the object 'xp_servicecontrol', database 'mssqlsystemresource', schema 'sys'.

Then, if for the non-sysadmin I run GRANT EXECUTE on master.dbo.xp_servicecontrol to [Domain\StandardUser] and rerun the original command it returns:

Error executing 'xp_servicecontrol': Permission denied. User must be a member of 'sysadmin' server role.

So that is the root of the problem. The permission is denied to anyone not a sysadmin.

Usually when using SSMS you can see the icon for SQL Server Agent and that will tell you whether it is up or down. That is not the same as a programmatic check, but it is a way for you to know.

Also, if your issue is that the SQL Agent might have failed, then the SQL Server Agent can be configured on the Properties form of the General tab to restart if it stops unexpectedly.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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