SQL Server – Find who made a database security change

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

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

A DBA asked me about tracking down the application which triggered a regular BACKUP request at an unexpected time.  This unexpected backup was causing performance issues for business users. He couldn’t see from the SQL Server Error logs an application name that could make a BACKUP request.   I suggested he use the SQL Server Default trace Audit Backup/Restore event. The Audit Backup/Restore event class occurs when a backup or restore command is executed

The Default Trace supplies very useful information for troubleshooting  SQL Server problems. There are a wide range of events audited. The default trace is lightweight and I’d recommend DBAs don’t turn off this trace. Read this post for general information - SQL Server – default trace FAQ.   Included in the post are details on listing all events traced.

 

I’ve used the default trace for different troubleshooting scenarios such as:

SQL LOG FILE AUTOGROW performance troubleshooting

Who made DDL table changes on the database.

 

When troubleshooting Backup or Restore Issues these questions are useful:

1)      What loginname is used for the Back \ Restore request?

2)      What was the start time?

3)      What was the  command text?

4)      What application triggered the request?

 

It is not a guarantee that all BACKUP \ RESTORE information is available , but some clues can offer direction for further direction.

 This query lists all the Backup \ Restore events on the SQL Server Instance. Note: the default setting of the default trace rolls over the trace file, so  backup the trace files for historical information. When the SQL Server instance restarts the default trace files are refreshed.

select e.name as eventclass,t.loginname, t.spid, t.starttime, 
t.textdata, t.objectid, t.objectname, t.databasename, 
t.hostname, t.ntusername, 
t.ntdomainname, t.clientprocessid, t.applicationname, t.error 
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
inner join sys.trace_events e on t.eventclass = e.trace_event_id
where eventclass=115

SQL Server - Who made DDL table changes on the database.

Question:

Some DDL changes have occurred on the SQL Server database. Can I find out who made the changes?

Answer:

Yes. The SQL Server default trace has the Object Altered event. 

Read this FAQ for details on SQL default trace

This script will list all Object Altered event. Add WHERE predicates on date and databasename to refine  the search and isolate the DDL change

 

select e.name as eventclass,
t.loginname, 
t.spid, 
t.starttime,
t.textdata, 
t.objectid, 
t.objectname, 
t.databasename, 
t.hostname, 
t.ntusername, 
t.ntdomainname, 
t.clientprocessid, 
t.applicationname, 
t.error 
FROM sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1f.[value] 
FROM sys.fn_trace_getinfo(NULL) f WHERE f.property = 2)), DEFAULT) T 
inner join sys.trace_events e on t.eventclass = e.trace_event_id 
where eventclass=164

 Read Also

ISO-11179 Naming Conventions and SQL DDL - SQL Server DBA

How to request SQL Server troubleshooting

SQL Server – Find last time of update on a table - SQL Server DBA