Subscribe

SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
SQL Server – Find who made a database security change

01 January,2013 by Jack Vamvas

A nightly SQL Server Agent batch job started failing suddenly , with a message about a database user missing.

The SQL Server Agent job history indicated the job ran successfully for 6 months. Either someone had restored the database to a much older version or a database user was deleted.

The SQL Server default trace has an event - Audit Add DB User  .  The event is recorded in the trace when a login is added\removed as a database user.

This query returns all events of the Audit Add DB User . Luckily , the trace file hadn’t yet rolled over. Keep in mind the SQL Server default trace file has 5 trace files @ 20 MB each. If the system is very busy , these files can roll over quickly

 Using the query – I was able to discover the TargetUserName, database name , time of change and under what login name the change occurred.

 

SELECT  te.trace_event_id,
TE.name AS [EventName] ,
T.EventSubClass,
T.TargetUserName,
        T.DatabaseName ,

        t.DatabaseID ,

        t.NTDomainName ,

        t.ApplicationName ,

        t.LoginName ,

        t.SPID ,

        t.Duration ,

        t.StartTime ,

        t.EndTime
       

FROM    sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1

                                                              f.[value]

                                                      FROM    sys.fn_trace_getinfo(NULL) f

                                                      WHERE   f.property = 2

                                                    )), DEFAULT) T

        JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id

WHERE   t.StartTime > GETDATE() -1
    and te.name  =   'Audit Add DB User Event'  
   

ORDER BY t.StartTime ; 


 Read More

SQL Server – default trace FAQ - SQL Server DBA

SQL Server – Audit Backup\Restore Event with SQL Default trace ...

How to create a SQL Server Security Audit - SQL Server DBA

Powershell sql server security audit - SQL Server DBA


Author: Jack Vamvas (http://www.sqlserver-dba.com)

******** *******

Subscribe to SQLServer-DBA.com

Latest posts delivered to you daily

Delivered by FeedBurner

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


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