• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 207
  • Last Modified:

SQL Server Auditing

Currently Running SQL Server 2008R2
I have a few production Databases
I need to set up auditing to track all changes to these servers and all databases on these servers.
For Example Altered Procedures or dropped indexes or changed passwords plus all other major changes
I need to make sure that it is lightweight since I do not want our production environment to suffer. What type of audit can I set up which is not too taxing on the system?
Or will auditing always have a negative impact on the production environment?
0
VitaminD
Asked:
VitaminD
1 Solution
 
jogosCommented:
Best way is to limit permissions to only a few. Channel the db-changes so they get 'authored' before applied. And so you also can have the possibility to get your db-changes in version control.  You must restore db of last week, no problem and you still have the scripts to do the db-changes which were done since then.

Then you still can 'audit' with DDL-triggers, they don't bother normal usage of your database
See at
http://msdn.microsoft.com/en-us/library/bb522542.aspx
http://msdn.microsoft.com/en-us/library/bb522542.aspx 
http://www.mssqltips.com/sqlservertip/2085/sql-server-ddl-triggers-to-track-all-database-changes/

Or 'Auditing'
http://www.appsecinc.com/presentations/Security_Auditing_MSSQL.pdf
http://msdn.microsoft.com/en-us/library/cc280386(v=sql.105).aspx
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now