THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Rambling DBA: Jonathan Kehayias

The random ramblings and rantings of frazzled SQL Server DBA

TSQL2sday: Using sys.dm_exec_sql_text() to get the calling statement

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:

image

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:

image

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
Published Tuesday, January 12, 2010 12:26 AM by Jonathan Kehayias
Filed under:

Comments

 

Jason Coombes (DatabaseJase) said:

Just as the thought entered my head that I needed a way to do this today this post arrives in my inbox. Thank you so very much! :)

January 12, 2010 6:15 AM
 

Geri Reshef said:

Well done!

January 16, 2010 11:41 PM
 

Leks said:

Hi Jon,

Simple method and clean explanation to cpature the SQL commands.

January 26, 2010 10:29 PM
 

Adam Machanic said:

According to T-SQL Tuesday rules as ratified by me in the first and second T-SQL Tuesday posts, the T-SQL

February 8, 2010 2:14 PM
Anonymous comments are disabled

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement