asked
Can default trace caputure login grant/deny enable/disable events?
I have been trying to extract this info from default trace unsuccessfully. If anybody can help I would be grateful
The grant/deny yes, per
http://www.simple-talk.com/sql/performance/the-default-trace-in-sql-server---the-power-of-performance-and-security-auditing/. The security audit portion is towards the bottom of the article. I'm not sure about the enable/disable. The above article is 2005, so I would assume it's the same for 2008/2012.
You can also use this query: SELECT t.EventID, t.ColumnID, e.name as Event_Description, c.name as Column_Description FROM ::fn_trace_geteventinfo(1) t JOIN sys.trace_events e ON t.eventID = e.trace_event_id JOIN sys.trace_columns c ON t.columnid = c.trace_column_id from
http://blogs.technet.com/b/beatrice/archive/2008/04/29/sql-server-default-trace.aspx to see what your particular default trace is capturing.
From test I did, it does not capture information pertaining to grant/deny connection and enable/disable the login. Now if you add/remove an user from a database it does capture that. I would expect to capture the instance level information would require C2 Audit to be enabled, or custom server-side trace. You don't specify the version of SQL Server you are working with but if you are using anything above SQL Server 2008 I would highly suggest looking into [SQL Audit][1]. **Edit** It appears as John has pointed out that the default trace is selective in what GRANT commands it captures pertaining to a login. Which seems odd to me, that is why I don't usually depend on the default trace for this information. [1]:
http://msdn.microsoft.com/en-us/library/cc280386.aspx
All I did was test granting and denying a login connection and default trace did not capture it in sql 2005.
We are using Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64) Sep 16 2010 19:43:16 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.0 (Build 6002: Service Pack 2) We manage SQL clusters in a datawarehouses, so I was interested in using default trace to diagnose security issues. Many thanks for all your help.
10 People are following this question.
