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 created a user and granted many permissions and roles to it including SQLAgentUserRole and SQLAgentReaderRole. My application checks the status of the agent to see if it is running or not, and whenever I am trying to run my application using the user I have created it is not able to identify a Running SQL Server Agent.

When I do the same thing using a user in the sysadmin role it works fine.

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 '14 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 '14 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 '14 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 '14 at 5:51

3 Answers 3

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

NONE :-)

Starting in SQL Server 2005, Microsoft provided a mechanism to infer permissions via a "proxy". The proxy for the permissions is either a Login (for Server-level permissions) or a User (for Database-level permissions). In either case, the principal (server or database) is created from either an Asymmetric Key or Certificate, is granted the appropriate permissions, and then the permissions get inferred by signing one or more pieces of code using ADD SIGNATURE. By doing this, you are giving the code the permissions that you had granted to that server or database principal. So now you can create a stored procedure or function to do a very specific thing, and effectively grant only that code the appropriate permissions to run properly. Then you just handle permissions naturally, by granting EXECUTE / SELECT permission to only those users or roles who should be allowed to perform that action.

In terms of executing xp_servicecontrol, if you grant EXECUTE on it to a User, then that User can run it to get info on ANY service, which is not nearly as fine-tuned as allowing for only running it to get info on one particular service.

With the inferred permissions method in mind, we could create a Login and place it in the sysadmin role, but we don't even need to go that far. Starting in SP1 of SQL Server 2008 R2, a new DMV was introduced that shows status information for SQL Server related Windows / NT Services: sys.dm_server_services. The only permission this DMV needs is VIEW SERVER STATE. But again, you don't even need to grant this permission to the User in question. You can:

  • Create a Function (UDF or Multistatement TVF) or Stored Procedure that encapsulates the very specific usage of this DMV
  • Create a Certificate
  • Create a Login based on that Certificate
  • Grant VIEW SERVER STATE to that Login
  • Create that same Certificate in the database where the User in question exists
  • Sign the Function and/or Stored Procedure with that Certificate
  • Grant the User in question EXECUTE on that Function and/or Stored Procedure

In this setup, the User isn't given any permissions outside of running a Stored Procedure or Function. And while the VIEW SERVER STATE permission allows for A LOT of info to be seen, that permission is only used to run whatever code is in the Stored Procedure or Function. And as long as that code isn't Dynamic SQL, then that permission can't be applied outside of that narrowly-defined usage (or outside of any other code that has been signed by that same certificate).

Worried that you might slip up and accidentally grant ALTER PROCEDURE to that User and then they can put in any code that requires that permission? WRONG! Any change at all to an object that is signed will drop the signature from that object. So even if someone does manage to change that Function or Stored Procedure, it will no longer be signed by the Certificate and hence will no longer be able to infer permissions from the Certificate-based Login. And even if that User somehow has permission to add a signature to an object, that won't help without the password used to create the Certificate. And if the User has access to your passwords (or deployment scripts that might have them), then you have serious flaws in your process and standards, in which case it isn't worth the time and energy to do all of this, and just put that person in the sysadmin role ;-).


Try the following:

Initial setup:

USE [Test];
GO -------------------------------------------------------------
-- DROP PROCEDURE dbo.CheckSqlAgent
CREATE PROCEDURE dbo.CheckSqlAgent
AS
SET NOCOUNT ON;

SELECT dss.[status], dss.[status_desc]
FROM   sys.dm_server_services dss
WHERE  dss.[servicename] LIKE N'SQL Server Agent (%';

GO -------------------------------------------------------------
-- DROP FUNCTION dbo.IsSqlAgentRunning
CREATE FUNCTION dbo.IsSqlAgentRunning()
RETURNS BIT
AS
BEGIN
  DECLARE @IsRunning BIT = 0;

  IF (EXISTS(SELECT dss.*
             FROM   sys.dm_server_services dss
             WHERE  dss.[servicename] LIKE N'SQL Server Agent (%'
             AND    dss.[status] = 4 -- Running
            )
     )
  BEGIN
    SET @IsRunning = 1;
  END;

  RETURN @IsRunning;
END;

GO -------------------------------------------------------------

CREATE USER [MrNoLogin] WITHOUT LOGIN;
GRANT EXECUTE ON dbo.CheckSqlAgent TO [MrNoLogin];
GRANT EXECUTE ON dbo.IsSqlAgentRunning TO [MrNoLogin];

Test #1:

SELECT SESSION_USER AS [SessionUser];

EXEC dbo.CheckSqlAgent;
SELECT dbo.IsSqlAgentRunning() AS [IsSqlAgentRunning];

Returns:

SessionUser
-----------
dbo


status  status_desc
------  -----------
4       Running


IsSqlAgentRunning
-----------------
1

Test #2:

EXECUTE AS USER='MrNoLogin';
SELECT SESSION_USER AS [SessionUser];

EXEC dbo.CheckSqlAgent;
SELECT dbo.IsSqlAgentRunning() AS [IsSqlAgentRunning];

Returns:

SessionUser
-----------
MrNoLogin


Msg 297, Level 16, State 1, Procedure CheckSqlAgent, Line 7
The user does not have permission to perform this action.

Msg 297, Level 16, State 1, Line 6
The user does not have permission to perform this action.

The Fix:

REVERT;
SELECT SESSION_USER AS [SessionUser];
-------------------------------------------------------------
USE [master];

-- DROP CERTIFICATE [DoStuffCert];
CREATE CERTIFICATE [DoStuffCert]
    ENCRYPTION BY PASSWORD = N'W0rdUp,Yo!'
    WITH SUBJECT = N'Certificate for Managing Special Permissions';

CREATE LOGIN [MrDoStuff] FROM CERTIFICATE [DoStuffCert];
GRANT VIEW SERVER STATE TO [MrDoStuff];

BACKUP CERTIFICATE [DoStuffCert]
    TO FILE = 'C:\TEMP\ViewSqlAgentStatus.CER'
    WITH PRIVATE KEY
    (
        FILE = 'C:\TEMP\ViewSqlAgentStatus.PVK',
        DECRYPTION BY PASSWORD = 'W0rdUp,Yo!',
        ENCRYPTION BY PASSWORD = 'DontStartNoneWontBeNone'
    );
-------------------------------------------------------------
USE [Test];

CREATE CERTIFICATE [DoStuffCert]
    FROM FILE = 'C:\temp\ViewSqlAgentStatus.CER'
    WITH PRIVATE KEY (
        FILE = 'C:\temp\ViewSqlAgentStatus.PVK',
        DECRYPTION BY PASSWORD = 'DontStartNoneWontBeNone',
        ENCRYPTION BY PASSWORD = 'W0rdUp,Yo!'
    );

ADD SIGNATURE TO dbo.CheckSqlAgent
    BY CERTIFICATE [DoStuffCert] WITH PASSWORD = 'W0rdUp,Yo!';

ADD SIGNATURE TO dbo.IsSqlAgentRunning
    BY CERTIFICATE [DoStuffCert] WITH PASSWORD = 'W0rdUp,Yo!';

Test #3:

EXECUTE AS USER='MrNoLogin';
SELECT SESSION_USER AS [SessionUser];

EXEC dbo.CheckSqlAgent;
SELECT dbo.IsSqlAgentRunning() AS [IsSqlAgentRunning];

REVERT;

And now both work for MrNoLogin :-).

share|improve this answer

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
1  
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 '14 at 20:59

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

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.