PRINT '1. Create Database [TestDB]'
--=======================================================================================
CREATE DATABASE [TestDB];
GO
USE [TestDB];

CREATE LOGIN [testdb_owner] WITH PASSWORD=N'123', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
CREATE USER [testdb_owner] FROM LOGIN [testdb_owner]
--make user db_owner
EXEC sp_addrolemember N'db_owner', N'testdb_owner'
GO

CREATE PROCEDURE dbo.SELECT_ALL_JOBS
AS	
	select * from msdb.dbo.sysjobs_view
GO

CREATE PROCEDURE dbo.START_HELLO_JOB
AS
	EXEC MSDB.DBO.sp_start_job @JOB_NAME = 'Hello'
GO

--End of create [TestDB]
--=======================================================================================

PRINT '2. Create/Install Certificate on [TestDB]'
--=======================================================================================
USE [TestDB];

CREATE CERTIFICATE [TestJobCertificate]
ENCRYPTION BY PASSWORD = '123' --password of the private key
WITH SUBJECT = 'Certificate to view and execute jobs',
START_DATE = '20110101', EXPIRY_DATE = '20990101';	
	
BACKUP CERTIFICATE [TestJobCertificate]
TO FILE = 'D:\TestJobCertificate.CER'
WITH PRIVATE KEY (FILE = 'D:\TestJobCertificate.PVK',
         ENCRYPTION BY PASSWORD = '123',
				 DECRYPTION BY PASSWORD = '123');
GO

--Create the certificate user
CREATE USER TestJobCertificateUser FROM CERTIFICATE [TestJobCertificate]
GO

--sign stored proc with certificate
ADD SIGNATURE TO OBJECT::[SELECT_ALL_JOBS]
BY CERTIFICATE [TestJobCertificate] 
WITH PASSWORD = '123'; --password of the private key

GRANT EXECUTE ON SELECT_ALL_JOBS TO TestJobCertificateUser
	
--sign stored proc with certificate
ADD SIGNATURE TO OBJECT::[START_HELLO_JOB]
BY CERTIFICATE [TestJobCertificate] 
WITH PASSWORD = '123'; --password of the private key

GRANT EXECUTE ON START_HELLO_JOB TO TestJobCertificateUser
--=======================================================================================
GO


PRINT '3. Create/Install Certificate on [MSDB]'
--=======================================================================================
USE [MSDB];
GO

--Create 'HELLO' JOB
--=============================================================================
DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job @job_name=N'Hello', 
		@enabled=1, 		 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT

EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Hello', 
		@step_id=1,
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'select ''HELLO''', 
		@database_name=N'msdb', 
		@flags=0

EXEC msdb.dbo.sp_add_jobserver @job_name=N'Hello' 
--=============================================================================

--CREATE CERTIFICATE FROM FILE
CREATE CERTIFICATE [TestJobCertificate]
FROM FILE = 'D:\TestJobCertificate.CER'
WITH PRIVATE KEY (FILE = 'D:\TestJobCertificate.PVK',
				 ENCRYPTION BY PASSWORD = '123',
				 DECRYPTION BY PASSWORD = '123');

--Create the certificate user
CREATE USER TestJobCertificateUser FROM CERTIFICATE [TestJobCertificate]
GO

--GRANT NECESSARY PERMISSIONS REQUIRED TO VIEW/EXECUTE JOBS
--THIS DATABASE ROLE IS REQUIRED IN ORDER FOR THE CERTIFICATE USER TO START JOBS HE DOES NOT OWN
EXEC sp_addrolemember N'SQLAgentOperatorRole', N'TestJobCertificateUser'
GO

--ADDITIONAL PERMISSIONS FOR TESTING
GRANT EXECUTE ON MSDB.DBO.sp_verify_job_identifiers TO [TestJobCertificateUser]
GRANT SELECT ON MSDB..SYSJOBS_VIEW TO [TestJobCertificateUser]
GRANT SELECT ON MSDB..SYSJOBS TO [TestJobCertificateUser]
GRANT EXECUTE ON MSDB.DBO.sp_start_job TO [TestJobCertificateUser]
GO


PRINT 'SETUP IS NOW COMPLETE AT THIS POINT...'

--=====================================================
PRINT '4. Testing Impersonation...'
--=====================================================
USE [TestDB];
GO
execute as login = 'testdb_owner'
select suser_name() as [context]

--this stmt will execute fine and return all records from msdb.dbo.sysjobs_view via the certificate
exec SELECT_ALL_JOBS

PRINT 'the following stmt will throw an error:'
PRINT 'Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers, Line 67'
PRINT 'The specified @job_name (''HELLO'') does not exist.'
PRINT 'however, if you run under the context of ''sa'', it will execute fine (but that defeats the purpose of certificate signing)'
PRINT 'MSDB.DBO.sp_verify_job_identifiers, is where the error gets raised. sp_verify_job_identifiers attempts to select from msdb.dbo.sysjobs_view where (name = @job_name); '
PRINT 'however no rows are returned, hence error # 14262 gets raised. On the other hand, when you selected from msdb.dbo.sysjobs_view through the stored proc, SELECT_ALL_JOBS, that is signed by the certificate, the rows DO get returned.'
PRINT '------------------------'
exec START_HELLO_JOB
PRINT '------------------------'

revert;


--CLEANUP CODE
/*
USE [MSDB];
GO
DROP USER [TestJobCertificateUser]
DROP CERTIFICATE [TestJobCertificate]

EXEC msdb.dbo.sp_delete_job @job_name='Hello'
GO

DROP Login [testdb_owner]
DROP DATABASE [TestDB]

--Delete certificate and private key from file
*/