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:
- MyDB
- ReportServer
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
and
USE MyDb;
SELECT pvt_key_encryption_type_desc FROM sys.certificates WHERE [name] = 'CodeSigningCertificate'
shows ENCRYPTED_BY_PASSWORD
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?
