Database Level SQL Audit in SQL Server 2012 Standard Edition

Question

Hi All,

I would like to have a database level SQL audit specification in my SQL server 2012 but my edition is just Standard Edition. Can I have some advice since I can't move on with this feature? Profiler and triggers method might be tough since I need to capture all the activities which made by some specific users within the database. (SELECT,INSERT,UPDATE,DELETE,CREATE commands and etc)

Shall I move on with 3rd party audit tools? If yes, which one is recommended? Sorry for asking if I'm not suppose to ask anything about 3rd party tools.

Really hope can get some help here. Highly appreciate and thanks.

Best Regards,

            Han

Answer 1

Hi All,

I would like to have a database level SQL audit specification in my SQL server 2012 but my edition is just Standard Edition. Can I have some advice since I can't move on with this feature? Profiler and triggers method might be tough since I need to capture all the activities which made by some specific users within the database. (SELECT,INSERT,UPDATE,DELETE,CREATE commands and etc)

Shall I move on with 3rd party audit tools? If yes, which one is recommended? Sorry for asking if I'm not suppose to ask anything about 3rd party tools.

Really hope can get some help here. Highly appreciate and thanks.

Best Regards,

            Han

you can try extended events but you cannot use this as an auditing tool and let it run 24*7 365 days on the database. EE perform better than profiler/trace or triggers...

refer this https://www.simple-talk.com/sql/database-administration/getting-started-with-extended-events-in-sql-server-2012/

may be you need to buy third  auditing tool.. i have not used any third tools for auditing...so, cannot recommend one.

---

Hope it Helps!!

Answer 2

Beside the above given suggestion, you may also look into LepideAuditor suite i.e.,(http://www.lepide.com/lepideauditor/sql-server.html ) that helps to audit SQL server changes into real time (based on server, database, objects, operations, and users).

Thus, you can get changes reporting in depth includes, who made the change or performed the operation, and the details of the change or operation. It sends customized email alerts to ensure the right staff are notified every time.

---

Lepide - Simplifying IT Management

Answer 3

My company is using this Microsoft gold partner tool for almost a year now, and we have been quite happy with it so far:

https://www.apexsql.com/sql_tools_audit.aspx

This tool contains a bunch of features for auditing access, changes, and security on SQL Server instances, objects and databases. It also audits queries, DDL and DML operations, security events, events on stored procedures and functions, etc.

For example, here you can find how to use it to audit SELECT statements in a few easy steps:

http://solutioncenter.apexsql.com/auditing-select-statements-on-sql-server/#ApexSQL_Audit

You should probably take a look at this audited events list, to make sure if this is what you are looking for:

http://knowledgebase.apexsql.com/audited-events-in-apexsql-audit/

Answer 4

Hello Han,

I have a customer whose environment is SQL Server 2012 Standard Edition and just like any company, they need auditing more and more every other day.

We tried Idera's Compliance Manager several years ago and decided to give it a go. After a while we had had a very nasty experience with this product.

After the announcement of SP1 with SQL Server 2016, now we are planning to upgrade as Database Level Auditing will be usable with Standard Edition. We want to take advantage of it and this is just one of the many advantages. I just wanted to remind you this, if this is an option for you too.

---

http://ekremonsoy.blogspot.com | http://www.ekremonsoy.com | @EkremOnsoy