Hello All, Please let me know how to audit a database SQL SEREVR 2000/2005/2008 like 1) Who all are logged in/ Accessing the particular Database 2) Who all have updated the Database or particular table with details Thanks Naina
1 - execute sp_who or sp_who2 and you will get details of current database connections 2 - you would need to have some process(es) in the individual databases and possibly the applications being used too, to track changes in order to get this information.
Data Auditing feature in SQL Server 2008 provides way to track and log events relating to your databases and servers. You can audit logons, password changes, data access and modification, and many other events.[SQL Server 2008 Auditing] In SQL Server 2005/2000, By enabling c2 Audit mode & creating custom DDL & DML Triggers you can do auditing up to certain extend. But enabling C2 Audit Level degrades the performance :
you can also enable audition by rt click on the instance -> go to->properties -> security -> enable failed logins, sucesfull logina and both, to know who had used the database just go to error log and see who had logged in and there database usage,