1

This question already has an answer here:

Is there a possibility to do this? All queries and actions performed in any database need to be logged, because of in house compliance.

I need information (and/or source) if we need MS SQL 2017 Standard or Enterprise in order to ensure that the DB Admin's activities (run, modify, etc queries...) are logged and not only the activities performed by non-admin users.

|improve this question

marked as duplicate by Erik Darling, LowlyDBA, John aka hot2use, Max Vernon sql-server Nov 5 '18 at 14:03

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • 1
    That would be either tracing (using either extended events directly or SQL Trace) or SQL Server Addit (which sits on top of Extended Events). At least those are the means that comes with SQL Server. Be prepared for lots and lots of logs. – Tibor Karaszi Oct 30 '18 at 7:30
  • The key is that even admins need to be logged, is this possible? – jsterr Oct 30 '18 at 7:40
  • @jsterr, as tibor karaszi said "be prepared for a lot and lots of logs", I remember I logged the results of sp_whoisactive to a table for every 30s and then after few weeks the entire table was like 120 gigs! – Biju jose Oct 30 '18 at 8:34
  • Thanks! Is tracing / logging part of Common Criteria Certification so Enterprise Certification is needed or am I fine with sql 2017 Std. – jsterr Oct 30 '18 at 9:12
  • 1
    No, the type of functionality I mentioned is available in lower editions. CC is something different. So you are fine to start evaluating your options using Std Ed. – Tibor Karaszi Oct 30 '18 at 13:19
5

Common Criteria Compliance is an enterprise feature

In the standard edition, well any edition, you can create a trace and trace "Audit Schema Object Access Event" that will give you a statement level audit. You can then filter on objects or databases to minimize the logs and create the trace as a server side trace.

The Audit Schema Object Access Event seems not to be accessible using extended events but the server side trace can be set up to stop the server if logging is no longer possible and is detailed enough to be acceptable as a compliant audit for PCI requirements

As I am corrected below, in later versions of SQL Server you can setup a database audit in all editions and audit all statements against a database or specific objects.

|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.