Grant a database role in current database with permission to execute a SP in msdb

Question

I have created a role in my current SQL Server 2012 database with db_datareader, db_datawriter, and execute permissions. A procedure in the current db calls sp_start_job in msdb.

How can I grant access to my database role to execute the procedure in msdb?

I tried executing the procedure in my current database which calls sp_start_job as owner and the user who is a member the user defined role is still not able to execute the procedure.

I have made the server role public for the login, and mapped it to msdb database, but I'm not able to grant permission. The command I'm trying to execute is:

GRANT EXECUTE ON OBJECT::[msdb].[dbo].[sp_start_job] TO [db_executor]

When I execute from msdb, the error I get is:

Cannot find the user 'db_executor', because it does not exist or you do not have permission'.

When I execute from the database in which the user defined role, db_executor is created, it throws this error:

You can only grant or revoke permissions on objects in the current database.

I don't want to add users to SQLAgentOperatorRole. The idea is to group all users in one role within the app's database and the members of this role should be able to run a procedure in the database which calls sp_start_job.

Answer 1

Here are the steps you should follow:

1. Get familiar with the material in SQL Server Agent Fixed Database Roles.
2. Decide which one is good for you

3. Use a script similar to the following one (but first choose specific msdb role) to achieve what you ask for:

   USE [master]
   GO 
   CREATE LOGIN [DOMAIN\user] FROM WINDOWS 
   GO 
   USE [msdb]
   GO
   CREATE USER [DOMAIN\user] FOR LOGIN [DOMAIN\user] 
   GO
   USE [msdb]
   GO
   EXEC sp_addrolemember N'SQLAgentOperatorRole', N'DOMAIN\user'
   GO
   

Answer 2

Microsoft created special roles that allow this sort of thing

"SQL Server Agent Fixed Database Roles"

Granting EXECUTE on sp_start_job won't work, because permissions and the checks made when starting a job are linked to role membership only.

USE msdb
ALTER ROLE SQLAgentOperatorRole ADD MEMBER DR_user;

Answer 3

This can be accomplished without giving the application nearly full control over SQL Agent jobs. Instead of creating the User in msdb, you create a Certificate in both the current DB and in msdb, and a few extra steps associated with the Certificate. The steps to do this are shown in the working example below:

Initial Setup and Test 1:

USE [master];
---
CREATE DATABASE [SecureJobStarter] COLLATE Latin1_General_100_CI_AS_SC;

CREATE LOGIN [CannotStartJob]
  WITH PASSWORD = N'YouCallThisAPassword?',
  DEFAULT_DATABASE = [master],
  CHECK_EXPIRATION = OFF,
  CHECK_POLICY = OFF;
GO
---

USE [SecureJobStarter];
GO
CREATE PROCEDURE [dbo].[StartJob]
(
    @JobName sysname
)
AS
SET NOCOUNT ON;

EXEC [msdb].[dbo].[sp_start_job] @job_name = @JobName;
GO
---
CREATE ROLE [JobStarter];

GRANT EXECUTE ON [dbo].[StartJob] TO [JobStarter];

CREATE USER [CannotStartJob] FOR LOGIN [CannotStartJob];
ALTER ROLE [JobStarter] ADD MEMBER [CannotStartJob];
GO
----------------------
-- TEST 1

USE [SecureJobStarter];

EXECUTE AS LOGIN = N'CannotStartJob';
SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];

EXEC(N'USE [msdb]; EXEC msdb.dbo.sp_start_job @job_name = N''StartJobTest'';');
/*
Msg 229, Level 14, State 5, Procedure sp_start_job, Line xxxxx
The EXECUTE permission was denied on the object 'sp_start_job',
      database 'msdb', schema 'dbo'.
*/


EXECUTE [dbo].[StartJob] N'StartJobTest';
/*
Msg 229, Level 14, State 5, Procedure sp_start_job, Line xxxxx
The EXECUTE permission was denied on the object 'sp_start_job',
      database 'msdb', schema 'dbo'.
*/

REVERT;
SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];
GO
----------------------

As you can see, there is currently no ability to execute sp_start_job, either directly in [msdb] (which the Login does at least have access to enter), or through the local stored procedure.

Create Certificate and Cert-based User, and Test 2:

USE [SecureJobStarter];

DECLARE @SQL2 NVARCHAR(MAX);
SET @SQL2 =  N'
-- Create in current DB...
CREATE CERTIFICATE [Permission:AgentOperator$Cert]
  ENCRYPTION BY PASSWORD = N''MyCertificate!MineMineMine!''
  WITH SUBJECT = N''Grant the SQLAgentOperatorRole Role'',
  EXPIRY_DATE = ''2099-12-31'';

DECLARE @CertificateBytes VARBINARY(MAX),
        @PrivateKeyBytes VARBINARY(MAX),
        @CertID INT;

SET @CertID = CERT_ID(N''Permission:AgentOperator$Cert'');

SELECT @CertificateBytes = CERTENCODED(@CertID),
       @PrivateKeyBytes = CERTPRIVATEKEY(@CertID,
           N''MyCertificate!MineMineMine!'', N''MyCertificate!MineMineMine!'');

-- Now recreate same Cert in [msdb]...
USE [msdb];

DECLARE @SQL3 NVARCHAR(MAX);
SET @SQL3 = N''
--------------------------------
CREATE CERTIFICATE [Permission:AgentOperator$Cert]
FROM BINARY = '' + CONVERT(NVARCHAR(MAX), @CertificateBytes, 1) + N''
WITH PRIVATE KEY
(
    BINARY = '' + CONVERT(NVARCHAR(MAX), @PrivateKeyBytes, 1) + N'',
    DECRYPTION BY PASSWORD = N''''MyCertificate!MineMineMine!'''',
    ENCRYPTION BY PASSWORD = N''''MyCertificate!MineMineMine!''''
);
--------------------------------
'';
EXEC(@SQL3);

CREATE USER [Permission:AgentOperator$User]
    FROM CERTIFICATE [Permission:AgentOperator$Cert];

ALTER ROLE [SQLAgentOperatorRole]
    ADD MEMBER [Permission:AgentOperator$User];
';

EXEC(@SQL2);
----

-- Finally, link the stored procedure in the current DB with the Certificate-based
-- User in [msdb] by signing the stored procedure...
ADD SIGNATURE
  TO [dbo].[StartJob]
  BY CERTIFICATE [Permission:AgentOperator$Cert]
  WITH PASSWORD = N'MyCertificate!MineMineMine!';
GO
----------------------
-- TEST 2

USE [SecureJobStarter];

EXECUTE AS LOGIN = N'CannotStartJob';
SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];

EXEC(N'USE [msdb]; EXEC msdb.dbo.sp_start_job @job_name = N''StartJobTest'';');
/*
Msg 229, Level 14, State 5, Procedure sp_start_job, Line xxxxx
The EXECUTE permission was denied on the object 'sp_start_job',
      database 'msdb', schema 'dbo'.
*/


EXECUTE [dbo].[StartJob] N'StartJobTest';
/*
Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers, Line xxxxx
The specified @job_name ('StartJobTest') does not exist.
*/

REVERT;
SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];
GO
----------------------

As you can see above, there is still no ability to directly execute sp_start_job, but this time the local stored procedure did get farther: it it was able to execute sp_start_job but then got an error on a sub-procedure call – sp_verify_job_identifiers – within sp_start_job.

Add [Counter] Signatures and Test 3:

I originally set this up to be counter signatures, but since then have learned that there is no practical difference between regular signatures and counter signatures in this case, and no meaningful / beneficial difference between them in general (so not sure why counter signatures even exist). You can use counter signatures here, but it does not appear to be necessary. They merely ensure that the start of the process is the signed proc, dbo.StartJob, such that one cannot start the process with the procs in [msdb]. BUT, given that the entire point of doing this module signing is that the user is never given access to [msdb], there was never any potential for that situation anyway.

USE [SecureJobStarter];

EXEC(N'
USE [msdb];

ADD COUNTER SIGNATURE -- "COUNTER" keyword to the left should be optional
  TO [dbo].[sp_start_job]
  BY CERTIFICATE [Permission:AgentOperator$Cert]
  WITH PASSWORD = N''MyCertificate!MineMineMine!'';

ADD COUNTER SIGNATURE -- "COUNTER" keyword to the left should be optional
  TO [dbo].[sp_verify_job_identifiers]
  BY CERTIFICATE [Permission:AgentOperator$Cert]
  WITH PASSWORD = N''MyCertificate!MineMineMine!'';

ADD COUNTER SIGNATURE -- "COUNTER" keyword to the left should be optional
  TO [dbo].[sp_sqlagent_notify]
  BY CERTIFICATE [Permission:AgentOperator$Cert]
  WITH PASSWORD = N''MyCertificate!MineMineMine!'';
');
GO
----------------------
-- TEST 3

USE [SecureJobStarter];

EXECUTE AS LOGIN = N'CannotStartJob';
SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];

EXEC(N'USE [msdb]; EXEC msdb.dbo.sp_start_job @job_name = N''StartJobTest'';');
/*
Msg 229, Level 14, State 5, Procedure sp_start_job, Line xxxxx
The EXECUTE permission was denied on the object 'sp_start_job',
      database 'msdb', schema 'dbo'.
*/


EXECUTE [dbo].[StartJob] N'StartJobTest'; -- SUCCESS!!!
/*
"Messages" tab: Job 'StartJobTest' started successfully.
Job History   : The job succeeded.  The Job was invoked by User CannotStartJob. 
*/

REVERT;
SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];
GO
----------------------

As you can see above, there is still no ability to directly execute sp_start_job, BUT this time the local stored procedure actually succeeded :-) !!

So, while this approach is admittedly not nearly as simple as the 2 lines required to add the app Login as a User to [msdb] and then add that User to the Role, it has the distinct advantage of not presenting any security risks. The Certificate-based User cannot be impersonated, and so the permission to execute sp_start_job is actually confined entirely to this local Stored Procedure. Meaning: if you allow the application Login access to msdb by creating a User for it and then add that User to the SQLAgentOperatorRole Database Role, then that Login not only can start any job, but can do anything that the SQLAgentOperatorRole Role allows for, resulting in:

😨 😱 😿

^{(fear, screaming, and crying)}. Why? Because developers (and hackers potentially) will be able to have the app code do anything they want (with regards to SQL Agent jobs) and never have to ask you about it, or even tell you about it (though you will likely find out eventually when someone submits a ticket saying that such-and-such a feature isn't doing what they expected and that was the first you heard about that feature which was introduced 6 - 12 months prior to you getting this support request ;-).

In contrast, the Module Signing approach (i.e. using Certificates and ADD [COUNTER] SIGNATURE ) is highly secure. You can hard code a single job name in the stored procedure so that the application can only start that one job. Or, you can allow for starting just a few specific jobs by having the stored procedure accept a TINYINT / INT parameter and using CASE or IF to map values 1, 2, 3,... to job names:

DECLARE @JobName sysname;

SET @JobName = CASE @JobNumber -- input param
                 WHEN 1 THEN N'this_job'
                 WHEN 2 THEN N'that_job'
                 WHEN 3 THEN N'what_job'
               END;

EXEC [msdb].[dbo].[sp_start_job] @job_name = @JobName;

If someone tries to get sneaky and update this stored procedure to do something else that would take advantage of the SQLAgentOperatorRole Role, then the signature gets dropped and there is no more link to the Certificate-based User in msdb and that feature starts getting an error, which comes back to you to investigate which means you will be able to review the change and if you don't approve, then you don't run ADD SIGNATURE again.

For more info, please see the following resources:

• PLEASE, Please, please Stop Using Impersonation, TRUSTWORTHY, and Cross-DB Ownership Chaining
• Module Signing Info