To find out the DML,DDL Events
As a DBA sometimes we should able to trace out the activities of users whoever are connecting to Database Servers. If any developer could make changes to objects like He can create Objects like (Stored Procedures,Views,Triggers) as well as he can drop procedures, views and triggers.
As a DBA We should suppose to track all these DML,DDL Events. And we can find out those activities using these.
First Step: Check whether your server properties.
Select * from sys.configurations where configuration_id = 1568
Second Step: If we run this below we can get the particular trace or all existing traces.
SELECT * FROM ::FN_TRACE_GETINFO(0)
GO
select * from ::fn_trace_getinfo(null)
go
select * from ::fn_trace_getinfo(DEFAULT)
In the result we can find the path of the trace file.
Third Step: We can run this query and find the trace events. This sys.trace_events catalogue view contains a list of all sql server events. These trace events do not change for a given permission of the Microsoft SQLServer 2005 Database engine.
select * from sys.trace_events
order by trace_event_id
And we can also run this query to find out
SELECT ntusername,loginname, objectname, e.category_id, textdata, starttime,spid,hostname, eventclass,databasename, e.name
FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log.trc',0)
INNER JOIN sys.trace_events e
ON eventclass = trace_event_id
INNER JOIN sys.trace_categories AS cat
ON e.category_id = cat.category_id
WHERE databasename = 'MyOwn' AND
objectname = 'MySpName' AND
cat.category_id = 5 AND --category objects
e.trace_event_id = 46 --object creation
(Query Source: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3883001&SiteID=1)
As a DBA We should suppose to track all these DML,DDL Events. And we can find out those activities using these.
First Step: Check whether your server properties.
Select * from sys.configurations where configuration_id = 1568
Second Step: If we run this below we can get the particular trace or all existing traces.
SELECT * FROM ::FN_TRACE_GETINFO(0)
GO
select * from ::fn_trace_getinfo(null)
go
select * from ::fn_trace_getinfo(DEFAULT)
In the result we can find the path of the trace file.
Third Step: We can run this query and find the trace events. This sys.trace_events catalogue view contains a list of all sql server events. These trace events do not change for a given permission of the Microsoft SQLServer 2005 Database engine.
select * from sys.trace_events
order by trace_event_id
And we can also run this query to find out
SELECT ntusername,loginname, objectname, e.category_id, textdata, starttime,spid,hostname, eventclass,databasename, e.name
FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log.trc',0)
INNER JOIN sys.trace_events e
ON eventclass = trace_event_id
INNER JOIN sys.trace_categories AS cat
ON e.category_id = cat.category_id
WHERE databasename = 'MyOwn' AND
objectname = 'MySpName' AND
cat.category_id = 5 AND --category objects
e.trace_event_id = 46 --object creation
(Query Source: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3883001&SiteID=1)
Comments