November 7, 2011 at 7:48 am
Is it possible to add a signature to a database trigger? If so, then how? Someting like:
ADD SIGNATURE TO [database_trigger_name]
BY CERTIFICATE [certificate_name]
results in:
Msg 15151, Level 16, State 1, Line 1
Cannot alter the object 'database_trigger_name', because it does not exist or
you do not have permission.
SY.
November 7, 2011 at 8:09 am
That sounds like a permission issue. You can add a signature to a trigger. http://msdn.microsoft.com/en-us/library/ms181700.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 7, 2011 at 8:32 am
Sean Lange (11/7/2011)
That sounds like a permission issue. You can add a signature to a trigger. http://msdn.microsoft.com/en-us/library/ms181700.aspx
Thanks, but it is not a permission issue. I am dbowner. Trigger is created by me. I have no problems adding signatute to a SP or DML trigger. It is a database trigger that I am having an issue with. It is a question on how do I reference database trigger in ADD SIGNATURE statement (assuming it is possible).
SY.
November 7, 2011 at 8:54 am
Your syntax looks right. I am guessing that since you are still uncertain about the ability of adding this signature to a trigger that you didn't look at the article I linked. Here is the first sentence.
Adds a digital signature to a stored procedure, function, assembly, or trigger.
The error message is not a syntax issue. It clearly says that the object you are trying to alter by adding your digital signature either doesn't exist or you don't have permission. So it is most likely that you either don't have permission to alter the trigger or you typed the name incorrectly.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 7, 2011 at 9:13 am
The error message is not a syntax issue. It clearly says that the object you are trying to alter by adding your digital signature either doesn't exist or you don't have permission.
Error tells object does not exist because, as I said, I do not know how to reference database trigger in ADD SIGNATURE statement. Database trigger does not belong to any schema. To drop it we need to use ON DATABASE clause, otherwise we get same error:
DROP TRIGGER [database_trigger]
Msg 3701, Level 11, State 5, Line 1
Cannot drop the trigger 'database_trigger', because it does not exist or you do not have permission.
And there is no ON DATABASE clause in ADD SIGNATURE statement. If I try using it I get:
ADD SIGNATURE TO [database_trigger] ON DATABASE
BY CERTIFICATE [cert_database_trigger]
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'ON'.
So again, question is how to reference database trigger in ADD SIGNATURE statement[/b].
SY.
November 7, 2011 at 9:18 am
what is the real name of the database trigger?
it looks to me like you keep running the script on the placeholder name [database_trigger] instead of the real trigger name...TR_Whatever_Insert or whatever the real trigger name is....
Lowell
November 7, 2011 at 9:36 am
Lowell (11/7/2011)
what is the real name of the database trigger?
It is real name:
CREATE TRIGGER [database_trigger]
ON DATABASE
FOR ALTER_PROCEDURE
AS
BEGIN
print 'database_trigger'
END;
CREATE CERTIFICATE [cert_database_trigger]
ENCRYPTION BY PASSWORD = 'Dummy12345'
WITH SUBJECT = 'database_trigger'
GO
ADD SIGNATURE TO [database_trigger]
BY CERTIFICATE [cert_database_trigger]
WITH PASSWORD = 'Dummy12345'
SY.
November 7, 2011 at 9:41 am
OH this is a ddl trigger. You can't add a signature to DDL triggers. http://social.msdn.microsoft.com/Forums/en/sqlsecurity/thread/1333eecd-4c66-43d4-ab8f-03511cad4174
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 7, 2011 at 10:21 am
Sean Lange (11/7/2011)
OH this is a ddl trigger.
Well, database trigger is DDL trigger. Anyway, thanks for the link.
SY.
November 7, 2011 at 11:15 am
solomon.yakobson (11/7/2011)
Sean Lange (11/7/2011)
OH this is a ddl trigger.Well, database trigger is DDL trigger. Anyway, thanks for the link.
SY.
Yeah just me being dense. 😛 Hope that helps explain it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 19, 2017 at 3:32 pm
Sean Lange - Monday, November 7, 2011 9:41 AMOH this is a ddl trigger. You can't add a signature to DDL triggers. http://social.msdn.microsoft.com/Forums/en/sqlsecurity/thread/1333eecd-4c66-43d4-ab8f-03511cad4174
Nor can you sign Logon Triggers. I have just added a Connect Suggestion requesting that they add support for these non-Schema-scoped Triggers:
Allow signing Database DDL Triggers and Server DDL and Logon Triggers - ADD SIGNATURE
Take care, Solomon...
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply