question

Naina_S avatar image
Naina_S asked

audit a database SQL Server 2000/2005/2008

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
sql-server-2008sql-server-2000
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks a lot Fatherjack for your response. Im sorry ...please let me know what processes you are talking about. Is there any external tool (3 party tool ) which we can download and use it for audit purpose. eg Table Site_1 - > i wanted to know who all touched this table and what all operations they carried out on this table. i wanted to know smething apart from sp_who or sp_who2 Thanks Naina
0 Likes 0 ·
DaniSQL avatar image
DaniSQL answered
Check out these resources, they are good starters [Understanding SQL Server Audit][1] [SQL Server 2000 Auditing][2] [Auditing in SQL Server 2008][3] [SQL Server Audit Articles from Sql-server-performance.com][4] [**SQL Server Auditing** by By Brian Kelley on SqlServerCentral.com][5] (you may need to subscribe) [SQL Server 2008 Audit ][6] [1]: http://msdn.microsoft.com/en-us/library/cc280386.aspx [2]: http://technet.microsoft.com/en-us/library/dd277388.aspx [3]: http://msdn.microsoft.com/en-us/library/dd392015(SQL.100).aspx [4]: http://www.sql-server-performance.com/articles/audit/index.aspx [5]: http://www.sqlservercentral.com/articles/Security/sqlserverauditingpart1/1451/ [6]: http://www.simple-talk.com/sql/database-administration/sql-server-audit-magic-without-a-wizard/
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Cyborg avatar image
Cyborg answered
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 [1]: http://blogs.msdn.com/b/manisblog/archive/2008/07/21/sql-server-2008-auditing.aspx
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

freshdba avatar image
freshdba answered
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,
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.