question

half fast dba avatar image
half fast dba 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
logintracedefault
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM avatar image
JohnM answered
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.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
Shawn_Melton avatar image
Shawn_Melton answered
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
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hmm, I just tested on a SQL Server 2008R2 with the default trace and it captured the a 'GRANT CONTROL SERVER TO [Test1]'. It also captured the adding of the login as well.
0 Likes 0 ·
All I did was test granting and denying a login connection and default trace did not capture it in sql 2005.
0 Likes 0 ·
That's good to know. Hopefully the OP will tell us what version of SQL Server they are using. I would concur with you, I usually don't utilize the default trace for this type of tracking of security events.
0 Likes 0 ·
half fast dba avatar image
half fast dba answered
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 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.