Follow sqlserver-dba.com

Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions

SQLServer-DBA.com Links

Dba_db2_button

dba-ninja.com

SQL Server DBA Jobs

How to find privilege changes with SQL Server Default Trace

30 January,2025 by Tom Collins

Question: I'm doing some sql server security privilege troubleshooting , because a customer has reported an incident  - they have privileges yesterday to a certain SQL table but today those privileges no longer. I'd like to know if there is a way to identify what changes were made and by which login those changes occured. Can you supply a method ?

 

Answer: The SQL Server Default trace is very good for this type of reporting - although  you have to capture the details fairly quickly - so the files don't roll over. 

Here is a SQL query that will read the SQL Server default trace and report on certain event classes. For this query these are types of events reported 

102 Audit Database Scope GDR Event
103 Audit Schema Object GDR Event
104 Audit Addlogin Event
105 Audit Login GDR Event
106 Audit Login Change Property Event
108 Audit Add Login to Server Role Event
109 Audit Add DB User Event
110 Audit Add Member to DB Role Event
111 Audit Add Role Event

 

 

The SQL query to use is :

DECLARE @tracefile VARCHAR(500)
-- Get path of default trace file
SELECT @tracefile = CAST(value AS VARCHAR(500))
FROM ::fn_trace_getinfo(DEFAULT)
WHERE traceid = 1
AND property = 2
-- Get security changes from the default trace
SELECT *
 FROM ::fn_trace_gettable(@tracefile, DEFAULT) trcdata -- DEFAULT means all trace files will be read
 INNER JOIN sys.trace_events evt ON trcdata.EventClass = evt.trace_event_id
 WHERE trcdata.EventClass IN (102, 103, 104, 105, 106, 108, 109, 110, 111)
 ORDER BY trcdata.StartTime



here is a query to identify failed logins :

SELECT  trc.*
FROM fn_trace_getinfo(default) AS inf
CROSS APPLY fn_trace_gettable (convert(nvarchar(255), inf.value),default ) AS trc
WHERE inf.property = 2 AND inf.value IS NOT NULL
AND trc.EventClass= 20 
ORDER BY trc.StartTime DESC

 

Related links 

Trace flag 610 and High performance data loading

How to use sxstrace.exe – diagnose side-by-side errors

Find who made a database name change with Object:Altered


Author: Tom Collins (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to find privilege changes with SQL Server Default Trace


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer