This week I have two posts for TSQL2sday. Initially I wrote my first blog post, but as luck would have it, today a second topic for TSQL2sday popped up through the comments on my recent SQL Server Central Article, Using a Certificate Signed Stored Procedure to Execute sp_send_dbmail. that actually made for a much better TSQL2sday post in my opinion so here it is. The comment regarded being able to call sys.dm_exec_sql_text() to get the calling statement inside of a trigger or stored procedure using the methods described in the article. However, there is an oversight in the expectations of what the ultimate outcome of this might actually be.
To demonstrate the problem associated with this, as a sysadmin on the SQL Server, run the following code:
USE [tempdb]
GO
CREATE TABLE Test
(RowID INT IDENTITY PRIMARY KEY)
GO
CREATE TRIGGER Audit_Test
ON Test
FOR INSERT, UPDATE, DELETE
AS
BEGIN
SELECT TEXT
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE session_id = @@SPID
END
GO
INSERT INTO Test DEFAULT VALUES;
GO
The output of this test will be similar to the following:
Obviously the intent isn’t to capture the CREATE TRIGGER statement for the cause of the data change, the intent here is to capture the call that ultimately lead to the triggers execution. To get this kind of information, you can’t use the DMF sys.dm_exec_sql_text(), you instead have to use DBCC INPUTBUFFER, To validate this simply ALTER the TRIGGER code like the following example:
ALTER TRIGGER Audit_Test
ON Test
FOR INSERT, UPDATE, DELETE
AS
BEGIN
DECLARE @TEMP TABLE
(EventType NVARCHAR(30), Parameters INT, EventInfo NVARCHAR(4000))
INSERT INTO @TEMP EXEC('DBCC INPUTBUFFER(@@SPID)')
SELECT EventInfo FROM @TEMP
END
GO
INSERT INTO Test DEFAULT VALUES;
GO
The output of this is:
What is really interesting is that while this also offers the expected output, it also doesn’t require any special permissions to execute. DBCC INPUTBUFFER can be used to get the statement for the current session without requiring any additional or special permissions. Additional permissions, VIEW SERVER STATE, are only required to gather information about other session_id’s.
Don’t forget to clean up after running this test:
DROP TABLE Test