May 7, 2018 at 11:48 pm
We've created a trigger as below to audit DDL events and now it is causing us frustrations as the following error is thrown every time a select * into xyz.dbo.x from xyz.dbo.a gets executed. Since, the account doesn't have permission on the audit table the query fails. Obviously, I don't want to give access to the underlying audit table, so need advice on how to overcome the same so the user can run the query w/o providing him/her direct access to the underlying audit table.
Msg 229, Level 14, State 5, Procedure trg_srv_audit_DDL_Event, Line 31 [Batch Start Line 3]
The INSERT permission was denied on the object 'srv_ddl_audit', database 'PQR', schema 'rmx'
CREATE TRIGGER [trg_srv_audit_DDL_Event] ON ALL SERVER FOR DDL_EVENTS
AS
SET NOCOUNT ON;
DECLARE @data XML = EVENTDATA();
--print convert(varchar(max),@data)
DECLARE @SQL NVARCHAR(200)
,@SysJobs_Job_ID UNIQUEIDENTIFIER = NULL
,@SysJobsSteps_Step_ID INT;
IF LEFT(APP_NAME(),23) = 'SQLAgent - TSQL JobStep'
BEGIN
SET @SQL = 'SET @GUID = CAST(' + SUBSTRING(APP_NAME(), 30, 34) + ' AS UNIQUEIDENTIFIER)'
EXEC sp_executesql @SQL, N'@guid UNIQUEIDENTIFIER OUT', @GUID = @SysJobs_Job_ID OUT
SET @SysJobsSteps_Step_ID = CAST(SUBSTRING(REVERSE(APP_NAME())
,2
,CHARINDEX(' pets '
,REVERSE(APP_NAME())
) - 1
) AS INT
)
END
IF @data.value('(/EVENT_INSTANCE/DatabaseName)[1]','SYSNAME') NOT IN ('','CREATE_STATISTICS','SSISDB','ABC')
BEGIN
INSERT
INTO PQR.rmx.srv_ddl_audit
(DatabaseName
,SchemaName
,ObjectName
,ObjectType
,SysJobs_Job_ID
,SysJobsSteps_Step_ID
,EventType
,TSQL
)
VALUES (@data.value('(/EVENT_INSTANCE/DatabaseName)[1]','SYSNAME')
,@data.value('(/EVENT_INSTANCE/SchemaName)[1]','SYSNAME')
,@data.value('(/EVENT_INSTANCE/ObjectName)[1]','SYSNAME')
,LEFT(@data.value('(/EVENT_INSTANCE/ObjectType)[1]','SYSNAME'),100)
,@SysJobs_Job_ID
,@SysJobsSteps_Step_ID
,LEFT(@data.value('(/EVENT_INSTANCE/EventType)[1]' ,'SYSNAME'),100)
,@data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
) ;
DECLARE @obj TABLE(
db_nm varchar(500),
schema_nm varchar(100),
object_nm varchar(100),
object_type varchar(100),
event_type varchar(100)
);
INSERT
@obj(db_nm, schema_nm, object_nm, object_type, event_type)
VALUES (
@data.value('(/EVENT_INSTANCE/DatabaseName)[1]','SYSNAME')
,@data.value('(/EVENT_INSTANCE/SchemaName)[1]','SYSNAME')
,@data.value('(/EVENT_INSTANCE/ObjectName)[1]','SYSNAME')
,LEFT(@data.value('(/EVENT_INSTANCE/ObjectType)[1]','SYSNAME'),100)
,LEFT(@data.value('(/EVENT_INSTANCE/EventType)[1]' ,'SYSNAME'),100)
);
END
GO
May 8, 2018 at 3:33 am
I'm confused, in all honestly. If someone has permission to CREATE objects on a database, and you are auditing events which include creating objects, then they should inherently have access to at least INSERT into the audit table, or they should not have access to be able to CREATE objects.
I suppose, otherwise, you could set up the trigger to execute under a different Login/User that does have permission to INSERT into the Audit table.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 8, 2018 at 4:31 am
You can mitigate this by using EXECUTE AS Clause (Transact-SQL)
😎
Did you post the full and complete trigger code? What is the purpose of the @obj table variable?
Further, I suggest you add the text() function to the XQuery, makes it more efficient as it prevents the reconstruct of the XML output
VALUES (
@data.value('(/EVENT_INSTANCE/DatabaseName/text())[1]','SYSNAME')
,@data.value('(/EVENT_INSTANCE/SchemaName/text())[1]','SYSNAME')
,@data.value('(/EVENT_INSTANCE/ObjectName/text())[1]','SYSNAME')
,LEFT(@data.value('(/EVENT_INSTANCE/ObjectType/text())[1]','SYSNAME'),100)
,@SysJobs_Job_ID
,@SysJobsSteps_Step_ID
,LEFT(@data.value('(/EVENT_INSTANCE/EventType/text())[1]' ,'SYSNAME'),100)
,@data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText/text())[1]','nvarchar(max)')
) ;
May 8, 2018 at 8:48 am
Eirikur Eiriksson - Tuesday, May 8, 2018 4:31 AMYou can mitigate this by using EXECUTE AS Clause (Transact-SQL)
😎
Did you post the full and complete trigger code? What is the purpose of the @obj table variable?
Further, I suggest you add the text() function to the XQuery, makes it more efficient as it prevents the reconstruct of the XML output
VALUES (
@data.value('(/EVENT_INSTANCE/DatabaseName/text())[1]','SYSNAME')
,@data.value('(/EVENT_INSTANCE/SchemaName/text())[1]','SYSNAME')
,@data.value('(/EVENT_INSTANCE/ObjectName/text())[1]','SYSNAME')
,LEFT(@data.value('(/EVENT_INSTANCE/ObjectType/text())[1]','SYSNAME'),100)
,@SysJobs_Job_ID
,@SysJobsSteps_Step_ID
,LEFT(@data.value('(/EVENT_INSTANCE/EventType/text())[1]' ,'SYSNAME'),100)
,@data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText/text())[1]','nvarchar(max)')
) ;
Do you mind me asking you where do I need to add "EXECUTE AS". Also, wouldn't it change the query context to log information about the login that is used in the execute as clause rather than using the original login info.
May 8, 2018 at 8:58 am
Feivel - Tuesday, May 8, 2018 8:48 AMDo you mind me asking you where do I need to add "EXECUTE AS". Also, wouldn't it change the query context to log information about the login that is used in the execute as clause rather than using the original login info.
The context of the query in the trigger would change, but the DDL change would still be made by the original Login/User.
As for where to put it; I believe Eirikur meant to link to the documentation: https://docs.microsoft.com/en-us/sql/t-sql/statements/execute-as-clause-transact-sql?view=sql-server-2017. Learning to read the documentation is an extremely important, and simple, skill that you need to learn. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 8, 2018 at 9:26 am
Thom A - Tuesday, May 8, 2018 8:58 AMFeivel - Tuesday, May 8, 2018 8:48 AMDo you mind me asking you where do I need to add "EXECUTE AS". Also, wouldn't it change the query context to log information about the login that is used in the execute as clause rather than using the original login info.The context of the query in the trigger would change, but the DDL change would still be made by the original Login/User.
As for where to put it; I believe Eirikur meant to link to the documentation: https://docs.microsoft.com/en-us/sql/t-sql/statements/execute-as-clause-transact-sql?view=sql-server-2017. Learning to read the documentation is an extremely important, and simple, skill that you need to learn. 🙂
Thank you for enlightening me with your golden words :rolleyes:
May 8, 2018 at 9:51 am
Feivel - Tuesday, May 8, 2018 9:26 AMThom A - Tuesday, May 8, 2018 8:58 AMFeivel - Tuesday, May 8, 2018 8:48 AMDo you mind me asking you where do I need to add "EXECUTE AS". Also, wouldn't it change the query context to log information about the login that is used in the execute as clause rather than using the original login info.The context of the query in the trigger would change, but the DDL change would still be made by the original Login/User.
As for where to put it; I believe Eirikur meant to link to the documentation: https://docs.microsoft.com/en-us/sql/t-sql/statements/execute-as-clause-transact-sql?view=sql-server-2017. Learning to read the documentation is an extremely important, and simple, skill that you need to learn. 🙂
Thank you for enlightening me with your golden words :rolleyes:
Sarcasm noted, but the fact is learning to read and comprehend technical documentation is a vital skill that seems to be on its way to extinction.
May 8, 2018 at 9:57 am
Feivel - Tuesday, May 8, 2018 9:26 AMThom A - Tuesday, May 8, 2018 8:58 AMFeivel - Tuesday, May 8, 2018 8:48 AMDo you mind me asking you where do I need to add "EXECUTE AS". Also, wouldn't it change the query context to log information about the login that is used in the execute as clause rather than using the original login info.The context of the query in the trigger would change, but the DDL change would still be made by the original Login/User.
As for where to put it; I believe Eirikur meant to link to the documentation: https://docs.microsoft.com/en-us/sql/t-sql/statements/execute-as-clause-transact-sql?view=sql-server-2017. Learning to read the documentation is an extremely important, and simple, skill that you need to learn. 🙂
Thank you for enlightening me with your golden words :rolleyes:
Tom, Lynn and others here are very experienced professionals, suggest you do consider the advice given!
😎
Tom posted the link I meant to post, new phone and my bad, sorry about that. On the other hand, you could have gotten the answer in minutes if you'd practiced your google-foo!
May 8, 2018 at 10:08 am
Eirikur Eiriksson - Tuesday, May 8, 2018 9:57 AMTom posted the link I meant to post, new phone and my bad, sorry about that. On the other hand, you could have gotten the answer in minutes if you'd practiced your google-foo!
I thought as much when I click the link and it took me to this topic (especially as you'd quoted the title of the relevant document's name). 🙂 At least you can paste links from your phone though. SSC doesn't let me paste (from my phone), for some reason. 🙁
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 8, 2018 at 10:12 am
Thom A - Tuesday, May 8, 2018 10:08 AMEirikur Eiriksson - Tuesday, May 8, 2018 9:57 AMTom posted the link I meant to post, new phone and my bad, sorry about that. On the other hand, you could have gotten the answer in minutes if you'd practiced your google-foo!I thought as much when I click the link and it took me to this topic (especially as you'd quoted the title of the relevant document's name). 🙂 At least you can paste links from your phone though. SSC doesn't let me paste (from my phone), for some reason. 🙁
Thanks again for the correction Tom!
😎
May 8, 2018 at 10:13 am
CREATE TRIGGER [trg_srv_audit_DDL_Event]
ON ALL SERVER
WITH EXECUTE AS '<login_name>' /* replace with your own login_name with authority to write to audit table, etc. */
FOR DDL_EVENTS
AS
SET NOCOUNT ON;
...rest_of_trigger...
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
May 8, 2018 at 10:23 am
Eirikur Eiriksson - Tuesday, May 8, 2018 9:57 AMFeivel - Tuesday, May 8, 2018 9:26 AMThom A - Tuesday, May 8, 2018 8:58 AMFeivel - Tuesday, May 8, 2018 8:48 AMDo you mind me asking you where do I need to add "EXECUTE AS". Also, wouldn't it change the query context to log information about the login that is used in the execute as clause rather than using the original login info.The context of the query in the trigger would change, but the DDL change would still be made by the original Login/User.
As for where to put it; I believe Eirikur meant to link to the documentation: https://docs.microsoft.com/en-us/sql/t-sql/statements/execute-as-clause-transact-sql?view=sql-server-2017. Learning to read the documentation is an extremely important, and simple, skill that you need to learn. 🙂
Thank you for enlightening me with your golden words :rolleyes:
Tom, Lynn and others here are very experienced professionals, suggest you do consider the advice given!
😎Tom posted the link I meant to post, new phone and my bad, sorry about that. On the other hand, you could have gotten the answer in minutes if you'd practiced your google-foo!
Do you think I doubt about that...at least based on the advice that I am getting from professionals.
May 8, 2018 at 10:27 am
ScottPletcher - Tuesday, May 8, 2018 10:13 AM
CREATE TRIGGER [trg_srv_audit_DDL_Event]
ON ALL SERVER
WITH EXECUTE AS '<login_name>' /* replace with your own login_name with authority to write to audit table, etc. */WITH EXECUTE AS '<login_name>' /* replace with your own login_name with authority to write to audit table, etc. */
FOR DDL_EVENTS
AS
SET NOCOUNT ON;
...rest_of_trigger...
Thanks! Scott...just what I needed...no more educating and straight to the point...lol
May 8, 2018 at 11:30 am
Feivel - Tuesday, May 8, 2018 10:27 AMScottPletcher - Tuesday, May 8, 2018 10:13 AM
CREATE TRIGGER [trg_srv_audit_DDL_Event]
ON ALL SERVER
WITH EXECUTE AS '<login_name>' /* replace with your own login_name with authority to write to audit table, etc. */WITH EXECUTE AS '<login_name>' /* replace with your own login_name with authority to write to audit table, etc. */
FOR DDL_EVENTS
AS
SET NOCOUNT ON;
...rest_of_trigger...Thanks! Scott...just what I needed...no more educating and straight to the point...lol
I consider it still to be "educating", just with more direct help. Some people don't have untold free hours when they need something fixed, sometimes they need it fixed now.
You really should learn how to read the MS command formats in their help, though. It's tremendously useful to help construct proper code.
For example, if you need to capture some columns from an INSERT, and someone tells you to "use the OUTPUT clause", you can read the MS command syntax to understand where to put the clause and how it works exactly.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
May 8, 2018 at 11:47 am
ScottPletcher - Tuesday, May 8, 2018 11:30 AMFeivel - Tuesday, May 8, 2018 10:27 AMScottPletcher - Tuesday, May 8, 2018 10:13 AM
CREATE TRIGGER [trg_srv_audit_DDL_Event]
ON ALL SERVER
WITH EXECUTE AS '<login_name>' /* replace with your own login_name with authority to write to audit table, etc. */WITH EXECUTE AS '<login_name>' /* replace with your own login_name with authority to write to audit table, etc. */
FOR DDL_EVENTS
AS
SET NOCOUNT ON;
...rest_of_trigger...Thanks! Scott...just what I needed...no more educating and straight to the point...lol
I consider it still to be "educating", just with more direct help. Some people don't have untold free hours when they need something fixed, sometimes they need it fixed now.
You really should learn how to read the MS command formats in their help, though. It's tremendously useful to help construct proper code.
For example, if you need to capture some columns from an INSERT, and someone tells you to "use the OUTPUT clause", you can read the MS command syntax to understand where to put the clause and how it works exactly.
No more comments...again thanks for piggy backing 😉
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply