I have created a stored procedure which requires permissions across a number of databases:
USE MyDB; CREATE PROCEDURE dbo.MyProc ( @ScheduleName NVARCHAR(260), @KeepSchedule BIT = 0 ) AS BEGIN SET NOCOUNT ON; DECLARE @ScheduleId UNIQUEIDENTIFIER; -- get the schedule id so we can delete SELECT @ScheduleId = s.ScheduleId FROM ReportServer.dbo.Schedule s WHERE s.[Name] = @ScheduleName AND s.EventType = 'SharedSchedule'; IF @ScheduleID IS NOT NULL BEGIN BEGIN TRY BEGIN TRAN -- delete the schedule IF @KeepSchedule = 0 BEGIN EXEC ReportServer.dbo.DeleteTask @ScheduleID=@ScheduleID; END; -- delete from our local table DELETE FROM MyDB.dbo.MyTable WHERE MyColumn = @ScheduleName; COMMIT END TRY BEGIN CATCH -- rethrow the error DECLARE @ErrorNumber INT = ERROR_NUMBER(); DECLARE @ErrorSeverity INT = ERROR_SEVERITY(); DECLARE @ErrorMessage NVARCHAR(MAX) = ERROR_MESSAGE() + ' Line ' + CAST(ERROR_LINE() AS NVARCHAR(5)); DECLARE @ErrorState INT = ERROR_STATE(); RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState); IF @@TRANCOUNT > 0 BEGIN ROLLBACK; END; END CATCH; END ELSE BEGIN RAISERROR (N'There was an error finding the schedule. Does this schedule exist?', 11, 1); END; END; GO
I want to give permissions to a user to run this stored procedure but since it accesses objects across two databases:
I am unable to take advantage of ownership chaining so am looking to use the code signing technique to grant the permissions.
I have created Certificates as follows:
USE MyDb; CREATE CERTIFICATE [CodeSigningCertificate] ENCRYPTION BY PASSWORD = 'MyStrongPassword' WITH EXPIRY_DATE = '2099-01-01', SUBJECT = 'Code Signing Cert'; -- copy the cert to report server database DECLARE @Cert NVARCHAR(4000) = CONVERT(NVARCHAR(4000), CERTENCODED(CERT_ID(N'CodeSigningCertificate')), 1); EXEC (N'USE [ReportServer]; CREATE CERTIFICATE [CodeSigningCertificate] FROM BINARY = ' + @Cert); GO -- create the code signing user for ReportServer and grant the perms USE ReportServer; CREATE USER [CodeSigningUser] FROM CERTIFICATE [CodeSigningCertificate];
then I sign the stored procedure and give the code signing user the relevent permission required within the stored procedure:
-- sign stored proc USE MyDb; ADD SIGNATURE TO dbo.MyProc BY CERTIFICATE [CodeSigningCertificate] WITH PASSWORD = 'MyStrongPassword'; -- give the code signing login the permissions it needs to run the procedure successfully USE ReportServer; GRANT SELECT ON dbo.Schedule TO CodeSigningUser; GRANT EXECUTE ON dbo.DeleteTask TO CodeSigningUser;
finally I grant execute to the user and run as that user:
GRANT EXECUTE ON dbo.MyProc TO [Domain\MyUser]; EXECUTE AS LOGIN = 'Domain\MyUser'; EXECUTE dbo.MyProc @ScheduleName = 'Test'; REVERT;
I get the error
Msg 50000, Level 14, State 5, Procedure MyProc, Line 41 [Batch Start Line 111] The EXECUTE permission was denied on the object 'sp_delete_job', database 'msdb', schema 'dbo'. Line 1
This error is thrown because
ReportServer.dbo.DeleteTask deletes from a table which has delete trigger:
CREATE PROCEDURE [dbo].[DeleteTask] @ScheduleID uniqueidentifier AS SET NOCOUNT OFF -- Delete the task with the given task id DELETE FROM Schedule WHERE [ScheduleID] = @ScheduleID GO
The Schedule table has an after delete trigger:
CREATE TRIGGER [dbo].[Schedule_DeleteAgentJob] ON [dbo].[Schedule] AFTER DELETE AS DECLARE id_cursor CURSOR FOR SELECT ScheduleID from deleted OPEN id_cursor DECLARE @next_id uniqueidentifier FETCH NEXT FROM id_cursor INTO @next_id WHILE (@@FETCH_STATUS <> -1) -- -1 == FETCH statement failed or the row was beyond the result set. BEGIN if (@@FETCH_STATUS <> -2) -- - 2 == Row fetched is missing. BEGIN exec msdb.dbo.sp_delete_job @job_name = @next_id -- delete the schedule END FETCH NEXT FROM id_cursor INTO @next_id END CLOSE id_cursor DEALLOCATE id_cursor GO ALTER TABLE [dbo].[Schedule] ENABLE TRIGGER [Schedule_DeleteAgentJob] GO
To try to remedy this, I created a certificate and CodeSigningUser in msdb:
-- copy the cert to msdb database USE MyDb; DECLARE @Cert NVARCHAR(4000) = CONVERT(NVARCHAR(4000), CERTENCODED(CERT_ID(N'CodeSigningCertificate')), 1); EXEC (N'USE [msdb]; CREATE CERTIFICATE [CodeSigningCertificate] FROM BINARY = ' + @Cert); -- create the code signing user for msdb and grant the perms USE msdb; CREATE USER [CodeSigningUser] FROM CERTIFICATE [CodeSigningCertificate]; GRANT EXECUTE ON dbo.sp_delete_job TO CodeSigningUser
If I run the procedure again:
EXECUTE AS LOGIN = 'Domain\MyUser'; EXECUTE dbo.MyProc @ScheduleName = 'Test'; REVERT;
I get the same error.
This question suggests the problem is that a trigger is another module and certificate permissions can not pass along to other modules in the chain and that the solution is to add a counter signature to the trigger:
USE [ReportServer]; ADD COUNTER SIGNATURE TO dbo.[Schedule_DeleteAgentJob] BY CERTIFICATE [CodeSigningCertificate] WITH PASSWORD = 'MyStrongPassword'; GO
but I get the error
Msg 15556, Level 16, State 1, Line 134 Cannot decrypt or encrypt using the specified certificate, either because it has no private key or because the password provided for the private key is incorrect.
If I run
USE ReportServer; SELECT pvt_key_encryption_type_desc FROM sys.certificates WHERE [name] = 'CodeSigningCertificate'
I see NO_PRIVATE_KEY
USE MyDb; SELECT pvt_key_encryption_type_desc FROM sys.certificates WHERE [name] = 'CodeSigningCertificate'
How can I copy the CodeSigning Certificate that was created using a password so that it is also encrypted by password, enabling it to countersign the trigger?