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