The query to find who created or dropped or altered object in database – SQL

— read all available traces.

DECLARE @current VARCHAR(500);

DECLARE @start VARCHAR(500);

DECLARE @indx INT;

SELECT @current = path

FROM sys.traces

WHERE is_default = 1;

SET @current = REVERSE(@current)

SELECT @indx = PATINDEX(‘%\%’, @current)

SET @current = REVERSE(@current)

SET @start = LEFT(@current, LEN(@current) – @indx) + ‘\log.trc’;

— CHNAGE FILER AS NEEDED

SELECT CASE EventClass

WHEN 46

THEN ‘Object:Created’

WHEN 47

THEN ‘Object:Deleted’

WHEN 164

THEN ‘Object:Altered’

END

,DatabaseName

,ObjectName

,HostName

,ApplicationName

,LoginName

,StartTime

FROM::fn_trace_gettable(@start, DEFAULT)

WHERE EventClass IN (

46

,47

,164

)

AND EventSubclass = 0

AND DatabaseID <> 2

ORDER BY StartTime DESC

Reference:- http://goo.gl/JHLVLo

Happy Querying 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s