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)

Comments

Popular posts from this blog

System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

Pre login Handshake or Connection Timeout Period

Transparent Data Encryption(TDE) with Master Key and Certificate in SQL Server