Sql-server – delegate SQL Agent rights without granting sysadmin rights

The problem is with the difference between logins and users. When you are granting the permissions you are working with a user. Only a login can have server level permissions such as sysadmin. I discussed this here if you are interested.

In the mean time I can tell you how to do what you need, however it's not the best idea in the world.

First create a seperate database, make SA the owner of that database, set the trustworthy property on for that database. Then create your usp_fts_parser stored procedure with EXECUTE AS OWNER. In this case OWNER is dbo. Also in this case dbo is SA. Because you made the database trustworthy it will allow users in it to access server level permissions. All of this means that your stored procedure will be able to act as a sysadmin. Next grant the login that you are using for the web application connect permissions to your new database. Then grant that user execute permissions on the stored procedure.

This is very important. ONLY grant the user execute permissions on the stored procedure. You have created a big security hole by turning on trustworthy with a database owned by a sysadmin so you need to be very very very careful who you give permissions to that database and what you allow them to do.

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 :-).