SET NOCOUNT, XACT_ABORT ON
USE master
go
CREATE DATABASE PlayJob
DECLARE @pwd char(37) = convert(char(36), newid()) + 'a'
DECLARE @sql nvarchar(MAX)
SELECT @sql = 'CREATE LOGIN PlayJob$owner ' +
              'WITH PASSWORD = ' + quotename(@pwd, '''')
EXEC(@sql)
ALTER LOGIN PlayJob$owner DISABLE
DENY CONNECT SQL TO PlayJob$owner
ALTER AUTHORIZATION ON DATABASE::PlayJob TO PlayJob$owner
go
-------------------------------------------------------------------------------------------------------------------------
CREATE LOGIN DrRobert WITH PASSWORD = 'He does everything he can'
CREATE LOGIN PlayJobAppLogin WITH PASSWORD = 'Here, There and Everythere'
go 
USE PlayJob
go
CREATE ROLE JobRole
CREATE USER DrRobert
CREATE USER PlayJobAppLogin
ALTER ROLE JobRole ADD MEMBER DrRobert
ALTER ROLE JobRole ADD MEMBER PlayJobAppLogin
GRANT EXECUTE ON SCHEMA::dbo TO JobRole
go
-------------------------------------------------------------------------------------------------------------------------
CREATE TABLE Jobtable(id      int          NOT NULL,
                      somedata varchar(40) NOT NULL,
                       CONSTRAINT pk_Jobtable PRIMARY KEY (id)
)
INSERT Jobtable (id, somedata) 
    VALUES (1, 'I''ve got blisters on my fingers!'),
           (2, 'With a Little Help from My Friends')
go
-------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE SillyJob AS
   UPDATE Jobtable 
   SET    id += 10, 
          somedata = reverse(somedata)
go
-------------------------------------------------------------------------------------------------------------------------
USE msdb
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name 
               FROM   msdb.dbo.syscategories 
               WHERE  name=N'[Uncategorized (Local)]' 
                 AND  category_class=1)
BEGIN
   EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', 
                                               @type=N'LOCAL', 
                                               @name=N'[Uncategorized (Local)]'
   IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'PlayJob', 
      @enabled=1, 
      @notify_level_eventlog=0, 
      @notify_level_email=0, 
      @notify_level_netsend=0, 
      @notify_level_page=0, 
      @delete_level=0, 
      @description=N'No description available.', 
      @category_name=N'[Uncategorized (Local)]', 
      @owner_login_name='PlayJobAppLogin', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep 
      @job_id=@jobId, 
      @step_name=N'PlayJobStep', 
      @step_id=1, 
      @cmdexec_success_code=0, 
      @on_success_action=1, 
      @on_success_step_id=0, 
      @on_fail_action=2, 
      @on_fail_step_id=0, 
      @retry_attempts=0, 
      @retry_interval=0, 
      @os_run_priority=0, @subsystem=N'TSQL', 
      @command=N'EXEC SillyJob', 
      @database_name=N'PlayJob', 
      @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, 
                                         @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, 
                                             @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
go
-----------------------------------------------------------------------------------------------------------------
USE PlayJob
go
CREATE PROCEDURE start_playjob AS
   EXEC msdb.dbo.sp_start_job 'PlayJob'
   WAITFOR DELAY '00:00:10'
   SELECT id, somedata FROM Jobtable ORDER BY id
go
EXEC start_playjob
-----------------------------------------------------------------------------------------------------------------
EXECUTE AS LOGIN = 'DrRobert'
EXEC start_playjob
go
REVERT
go
---------------------------------------------------------------------------------------------------------------
USE msdb
go
CREATE CERTIFICATE [SIGN PlayJob_start_playjob] 
ENCRYPTION BY PASSWORD = 'Eight Days a Week'
WITH SUBJECT = '"EXEC ON sp_start_job - SQLAgentOperatorRole"'

CREATE USER [SIGN PlayJob_start_playjob] 
    FROM CERTIFICATE [SIGN PlayJob_start_playjob]

GRANT EXECUTE ON sp_start_job TO [SIGN PlayJob_start_playjob]
ALTER ROLE SQLAgentOperatorRole ADD MEMBER [SIGN PlayJob_start_playjob]
go
---------------------------------------------------------------------------------------------------------------
USE msdb
go
DECLARE @public_key varbinary(MAX) = 
            certencoded(cert_id('SIGN PlayJob_start_playjob')),
        @private_key varbinary(MAX) = 
            certprivatekey(cert_id('SIGN PlayJob_start_playjob'), 
                           'Eight Days a Week', 
                           'Eight Days a Week'),
        @sql nvarchar(MAX)

--SELECT @public_key, @private_key

SELECT @sql = 
   'CREATE CERTIFICATE [SIGN PlayJob_start_playjob]
    FROM BINARY = ' + convert(varchar(MAX), @public_key, 1) + '
    WITH PRIVATE KEY
        (BINARY = ' + convert(varchar(MAX), @private_key, 1) + ',
         DECRYPTION BY PASSWORD = ''Eight Days a Week'',
         ENCRYPTION BY PASSWORD = ''Eight Days a Week'')'

PRINT convert(varchar(MAX), @sql)

EXEC PlayJob.sys.sp_executesql @sql
go
-----------------------------------------------------------------------------------------------------------------
USE PlayJob
go
ADD SIGNATURE TO start_playjob BY CERTIFICATE [SIGN PlayJob_start_playjob]
    WITH PASSWORD = 'Eight Days a Week' 
go
----------------------------------------------------------------------------------------------------------------
EXECUTE AS LOGIN = 'DrRobert'
EXEC start_playjob
go
REVERT
go
---------------------------------------------------------------------------------------------------------------
USE msdb
go
ADD COUNTER SIGNATURE TO sp_start_job 
   BY CERTIFICATE [SIGN PlayJob_start_playjob] WITH PASSWORD = 'Eight Days a Week'
ADD COUNTER SIGNATURE TO sp_sqlagent_notify 
   BY CERTIFICATE [SIGN PlayJob_start_playjob] WITH PASSWORD = 'Eight Days a Week'
ADD COUNTER SIGNATURE TO sp_verify_job_identifiers 
   BY CERTIFICATE [SIGN PlayJob_start_playjob] WITH PASSWORD = 'Eight Days a Week'
go
----------------------------------------------------------------------------------------------------------------
USE PlayJob
go
EXECUTE AS LOGIN = 'DrRobert'
EXEC start_playjob
go                    
REVERT
go
--------------------------------------------------------------------------------------------------------------
USE msdb
GRANT EXECUTE ON sp_start_job TO public
EXECUTE AS LOGIN = 'DrRobert'
EXEC sp_start_job 'PlayJob'
go
REVERT
REVOKE EXECUTE ON sp_start_job FROM public
go
---------------------------------------------------------------------------------------------------------------
SELECT * 
FROM   msdb.dbo.sysjobhistory
WHERE  job_id IN (SELECT job_id FROM msdb.dbo.sysjobs WHERE name = 'PlayJob')
go
---------------------------------------------------------------------------------------------------------------
USE msdb
go
DROP COUNTER SIGNATURE FROM sp_start_job 
     BY CERTIFICATE [SIGN PlayJob_start_playjob]
DROP COUNTER SIGNATURE FROM sp_sqlagent_notify 
     BY CERTIFICATE [SIGN PlayJob_start_playjob]
DROP COUNTER SIGNATURE FROM sp_verify_job_identifiers 
     BY CERTIFICATE [SIGN PlayJob_start_playjob]
REVOKE EXECUTE ON sp_start_job FROM [SIGN PlayJob_start_playjob]
ALTER ROLE SQLAgentOperatorRole DROP MEMBER [SIGN PlayJob_start_playjob]
go
---------------------------------------------------------------------------------------------------------------
USE msdb
go
CREATE USER jobstartuser WITHOUT LOGIN
GRANT EXECUTE ON sp_start_job TO jobstartuser
ALTER ROLE SQLAgentOperatorRole ADD MEMBER jobstartuser
go
---------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE start_job_wrapper @name sysname 
   WITH EXECUTE AS 'jobstartuser' AS
   IF @name IN ('PlayJob')
      EXEC sp_start_job @name
   ELSE 
      RAISERROR('Attempt to start unauthorised job "%s".', 16, 1, @name)
go
---------------------------------------------------------------------------------------------------------------
CREATE ROLE PlayJobStarters
GRANT EXECUTE ON start_job_wrapper TO PlayJobStarters
CREATE USER DrRobert 
ALTER ROLE PlayJobStarters ADD MEMBER DrRobert
go
---------------------------------------------------------------------------------------------------------------
USE PlayJob
go
EXECUTE AS LOGIN = 'DrRobert'
EXEC msdb.dbo.start_job_wrapper 'PlayJob'
go
REVERT
WAITFOR DELAY '00:00:10'
SELECT id, somedata FROM Jobtable ORDER BY id
go
---------------------------------------------------------------------------------------------------------------
USE msdb
go
DROP USER DrRobert
DROP ROLE PlayJobStarters
GRANT EXECUTE ON start_job_wrapper TO [SIGN PlayJob_start_playjob]
go
---------------------------------------------------------------------------------------------------------------
USE PlayJob
go
ALTER PROCEDURE start_playjob AS
    EXEC msdb.dbo.start_job_wrapper 'PlayJob'
    WAITFOR DELAY '00:00:10'
    SELECT id, somedata FROM Jobtable ORDER BY id
go
ADD SIGNATURE TO start_playjob BY CERTIFICATE [SIGN PlayJob_start_playjob]
    WITH PASSWORD = 'Eight Days a Week'
go
---------------------------------------------------------------------------------------------------------------
EXECUTE AS LOGIN = 'DrRobert'
EXEC start_playjob
go
REVERT
go
---------------------------------------------------------------------------------------------------------------
USE msdb
go
DROP PROCEDURE start_job_wrapper
DROP USER jobstartuser
go
---------------------------------------------------------------------------------------------------------------
USE master
go
DECLARE @pwd char(37) = convert(char(36), newid()) + 'a'
DECLARE @sql nvarchar(MAX)
SELECT @sql = 'CREATE LOGIN jobstartuser ' +
              'WITH PASSWORD = ' + quotename(@pwd, '''')
EXEC(@sql)
ALTER LOGIN jobstartuser DISABLE
DENY CONNECT SQL TO jobstartuser
go
USE msdb
go
CREATE USER jobstartuser
GRANT EXECUTE ON sp_start_job TO jobstartuser 
ALTER ROLE SQLAgentOperatorRole ADD MEMBER jobstartuser
go
---------------------------------------------------------------------------------------------------------------
USE PlayJob
go
CREATE USER jobstartuser
go
ALTER PROCEDURE start_playjob AS
   EXECUTE AS LOGIN = 'jobstartuser'
       EXEC msdb.dbo.sp_start_job 'PlayJob'
   REVERT
   WAITFOR DELAY '00:00:10'
   SELECT id, somedata FROM Jobtable ORDER BY id
go
---------------------------------------------------------------------------------------------------------------
/* In GrantPermsToSP_server.sql use this parameter setting:
DECLARE @database nvarchar(260) = 'PlayJob',
        @procname nvarchar(520) = 'start_playjob'

-- The permissions to grant through the certificate. Leave table empty
-- to only remove current permissions.
DECLARE @perms TABLE (perm nvarchar(400) NOT NULL PRIMARY KEY)
INSERT @perms VALUES
   ('IMPERSONATE ON LOGIN::jobstartuser')
*/
---------------------------------------------------------------------------------------------------------------
EXECUTE AS LOGIN = 'DrRobert'
EXEC start_playjob
go
REVERT
go
---------------------------------------------------------------------------------------------------------------
USE msdb
GO
EXEC msdb.dbo.sp_delete_job @job_name=N'PlayJob', @delete_unused_schedule=1
go
DROP USER jobstartuser
go
DROP USER [SIGN PlayJob_start_playjob]
go
DROP CERTIFICATE [SIGN PlayJob_start_playjob]
go
USE master
go
ALTER DATABASE PlayJob SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE PlayJob
go
DROP LOGIN DrRobert
go
DROP LOGIN PlayJobAppLogin
go
DROP LOGIN PlayJob$owner
go
DROP LOGIN "SIGN [PlayJob].[dbo].[start_playjob]"
go
DROP CERTIFICATE "SIGN [PlayJob].[dbo].[start_playjob]"
go
DROP LOGIN jobstartuser
go