USE master
go
-- Create a test login.
CREATE LOGIN testuser WITH PASSWORD = 'CeRT=0=TeST'
go
-- Create test database.
CREATE DATABASE jobstarttest
go
USE msdb
-- Create certificate in msdb.
CREATE CERTIFICATE jobstartcert
   ENCRYPTION BY PASSWORD = 'Strawberry Fields Forever'
   WITH SUBJECT = 'To permit starting the Testjob',
   START_DATE = '20020101', EXPIRY_DATE = '20200101'
go
-- Create a user for the certificate.
CREATE USER jobstartcert_user FROM CERTIFICATE jobstartcert
go
-- Grant rights for the certificate login to run jobs.
EXEC sp_addrolemember SQLAgentOperatorRole, jobstartcert_user
go
-- Counter-sign sp_start_job and its subprocedures.
ADD COUNTER SIGNATURE TO sp_start_job BY CERTIFICATE jobstartcert
    WITH PASSWORD = 'Strawberry Fields Forever'
ADD COUNTER SIGNATURE TO sp_verify_job_identifiers BY CERTIFICATE jobstartcert
    WITH PASSWORD = 'Strawberry Fields Forever'
ADD COUNTER SIGNATURE TO sp_sqlagent_notify BY CERTIFICATE jobstartcert
    WITH PASSWORD = 'Strawberry Fields Forever'
go
-- Get the certificate bytes into a temp table so we can use it in 
-- the target database.
CREATE TABLE #keys (pubkey varbinary (MAX) NOT NULL,
                    privkey varbinary(MAX) NOT NULL)
INSERT #keys (pubkey, privkey)
   SELECT certencoded(cert_id('jobstartcert')),
          certprivatekey(cert_id('jobstartcert'), 'Looking through a Glass Onion',
                                                  'Strawberry Fields Forever')
go
-- Move to test database.
USE jobstarttest
go
-- Create a database user for the test login.
CREATE USER testuser
go
-- Create a procedure that starts a certain job.
CREATE PROCEDURE start_this_job AS
   EXEC msdb..sp_start_job 'Testjob'
go
-- Give test user right to execute the procedure.
GRANT EXECUTE ON start_this_job TO testuser
go
-- Import the certificate we created in msdb into the test database.
DECLARE @sql nvarchar(MAX)
SELECT @sql = 
   'CREATE CERTIFICATE jobstartcert 
    FROM BINARY = ' + convert(nvarchar(MAX), pubkey, 1) + '
    WITH PRIVATE KEY (BINARY = ' + convert(nvarchar(MAX), privkey, 1) + ',
                      DECRYPTION BY PASSWORD = ''Looking through a Glass Onion'',
                      ENCRYPTION BY PASSWORD = ''Fixing a Hole'')'
FROM #keys

PRINT @sql
EXEC (@sql)
DROP TABLE #keys
go
-- Delete the files.
EXEC master..xp_cmdshell 'DEL C:\temp\jobstartcert.*', 'no_output'
go
-- Sign the test procedures.
ADD SIGNATURE TO start_this_job BY CERTIFICATE jobstartcert
    WITH PASSWORD = 'Fixing a Hole'
go
-- Switch to the test user.
EXECUTE AS LOGIN = 'testuser'
go
-- Start the job, this succeeds.
EXEC start_this_job
go
-- Back to ourselves.
REVERT
go
-- Clean up.
USE msdb
go
DROP COUNTER SIGNATURE FROM sp_sqlagent_notify
   BY CERTIFICATE jobstartcert
DROP COUNTER SIGNATURE FROM sp_verify_job_identifiers
   BY CERTIFICATE jobstartcert
DROP COUNTER SIGNATURE from sp_start_job
   BY CERTIFICATE jobstartcert
DROP USER jobstartcert_user
DROP CERTIFICATE jobstartcert
go
USE master
go
DROP DATABASE jobstarttest
DROP LOGIN testuser