Trace DDL & DCL operations in SQL Server Log – fn_trace_gettable

Did you know that starting with SQL 2005 all future versions of SQL includes the default trace of all DDL or DCL modifications done on all DB objects?

I was inspired by Jonathan Kehayias blog to dig deeper and check other useful output from the default trace.

Let me remind you what are Data Definition Language (DDL) and Data Control language (DCL) statements:
DDL statements: are used to define the database structure or schema. Some examples:

  • CREATE – to create objects in the database
  • ALTER – alters the structure of the database
  • DROP – delete objects from the database
  • TRUNCATE – remove all records from a table, including all spaces allocated for the records are removed
  • COMMENT – add comments to the data dictionary
  • RENAME – rename an object

DCL statements : are used to manage users permissions. Some examples:

  • GRANT – gives user’s access privileges to database
  • REVOKE – withdraw access privileges given with the GRANT command

For example, I am pretty sure that you wanted at least once to find out if somebody with access on Production databases altered a certain Procedure (by create and drop), Deleted an Object or Created without letting you know or even by mistake? As well, granting rights to Logins just because some people can do that is a very bad habit with which  maybe you had to deal at some point. Windows Administrators or DEV team leaders can take advantage of their high rights and escalate the DBA’s  in some urgent matter.

Asking around didn’t bring you any answer right? Perhaps the person who did that doesn’t even know, or maybe it was part of a faulty deployment?

The modification may have caused some errors, or lost in permissions… At last, it is very useful to know right away if this kind of modification took place and you or your team didn’t do that.

To see what Events are logged – query the fn_trace_geteventinfo (trace_id) function by joining it with sys.trace_categories and sys.trace_events tables:

SELECT DISTINCT tge.eventid, AS CategoryName, AS EventName 
FROM fn_trace_geteventinfo(1) AS tge INNER JOIN sys.trace_events AS te ON tge.eventid = te.trace_event_id 
INNER JOIN sys.trace_categories AS tc ON te.category_id = tc.category_id
ORDER BY CategoryName, EventName

If you are interested in collecting all information in the last week displaying all Created/Deleted/Altered Objects as well as db_roles or server roles assigned to new Logins not by you, than you can issue following SQL code – tested only on SQL 2008 :



IF (object_id('tempdb.dbo.#ddl_stmt') IS NOT Null)
   DROP TABLE tempdb.dbo.#ddl_stmt
CREATE TABLE   tempdb.dbo.#ddl_stmt
	   HostName nvarchar(250) null, 
       ApplicationName  nvarchar(250) null, 
       NTUserName  nvarchar(250) null, 
       NTDomainName  nvarchar(250) null, 
       LoginName  nvarchar(250) null, 
       SPID int null, 
       EventClass int null, 
       Name  nvarchar(250) null,
       EventSubClass int null,      
       TEXTData  nvarchar(250) null, 
       StartTime datetime, 
       ObjectName  nvarchar(250) null, 
       DatabaseName  nvarchar(250) null, 
       TargetLoginName nvarchar(250) null,
       TargetUserName nvarchar(250) null )
DECLARE @filename VARCHAR(255) 
SELECT @FileName = SUBSTRING(path, 0, LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc'  
FROM sys.traces   
WHERE is_default = 1;  

DECLARE @starttime datetime = getdate () -7

INSERT INTO tempdb.dbo.#ddl_stmt ( [HostName]
SELECT gt.HostName, 
FROM [fn_trace_gettable](@filename, DEFAULT) gt 
JOIN master.sys.trace_events te ON gt.EventClass = te.trace_event_id 
WHERE EventClass in (164, 46,47,108, 110, 152) and gt.StartTime > = @starttime
and gt.LoginName not in ('NT AUTHORITY\NETWORK SERVICE') 

select * from #ddl_stmt
  1. Cred ca e o mica greseala gramaticala acolo .. “to dig dipper”- deeper.

  2. Merci A_M pentru feedback 🙂

  3. hi yrushka from Romania
    I try to find who change ‘sa’ password in our sql server. i find good results by this query :

    “SELECT LoginName ,TargetLoginName, SessionLoginName, HostName, StartTime , LoginSid, spid , TextData
    FROM fn_trace_gettable(‘C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\log_92.trc’, default)
    where TargetLoginName is not null”

    but now need to find ‘Operation’ for each row that happend. like Delete, Create or change password by SessionLoginName.
    i will be glad if help me.
    best wish
    waiting to you

  4. Hi, I found this that might help you. By default SQL does not keep track of password changes.

Leave a Comment

NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>