Audit Log.

Question

Audit Log.

ben.brugman

SSChampion

Points: 13369
More actions
November 28, 2019 at 2:23 pm

#3701914

Our SQL-server versions are not all up to date.
For myself I am not up to date at all.
Although I have worked a lot with SQL-server engines, I normally work with constructions which work in the standard (and express) editions of SQL-server. So my question is:
What is needed for an Audit Log ?
(System/version ???)
What are the possibilities ?
In my version of SQL-server and SSMS I can not locate the Audit possibilities. (But I do not have the Enterprise edition and I do not have a recent SQL-server). **)
Ben
**)
One of the reasons with staying behind is that I limit myself not to use new functionality, some of our systems and the systems customers use are older systems and often the standard edition.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 1

what are you trying to audit?

you can audit data changes (you'll get lots of suggestions), maybe schema changes (that one is my speciality), configuration changes ?

in sql server management studio there Is a "schema changes history report" - but it is not too good - I've used Database triggers and logged them to a table for DDL and permission changes.

I might be able to help if you can give me more detail about what you are trying to do

MVDBA

ben.brugman SSChampion Points: 13369 More actions · Answer 2

Mainly (???)

Data changes.

A requirement is that something similar to SQL Anywhere Auditing is implemented. For two main purposes:

1. To discover problems with the database and/or database software. (Debugging problems).

2. To track changes to inspect what users have done. (Mistakes or doing something correctly in the wrong way).

Schema are not the priority.

Ben

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 3

I think on this one you will have to write an audit system that suits your own needs.

It's going to be triggers that write to a log table, but trust me... expect a lot of traffic

MVDBA

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

I think Audit is available in Standard Edition (depending on version), just with reduced functionality. Or you can use an Extended Events session, which I think is what an Audit is behind the scenes.

John

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 5

MVDBA (Mike Vessey)

SSC-Insane

Points: 21797

November 28, 2019 at 4:13 pm

#3701950

is CDC an option ?

MVDBA

ben.brugman SSChampion Points: 13369 More actions · Answer 6

Maybe I was not clear (sorry):

Maybe the answer is: "Use the most recent Enterprise edition."

At this moment I do not use the Enterprise edition, that's why I asked the question. If Enterprise Edition is the answer, that's ok. Only I can not check that for this moment. So the edition I am using at the moment is not the limitation, but is what I have access to. So If I/we/the customer wants audit logging and another edition is needed, that's a completely acceptable answer.

Sorry that the question was not clear on this point.

Ben

(This was written on Friday 29 november, but did not hit the Submit button correctly, oops) 🙁

This reply was modified 4 years, 4 months ago by ben.brugman.

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 7

HI Ben

for my own learning, why "the most recent version of enterprise" fixes your needs

MVDBA

ben.brugman SSChampion Points: 13369 More actions · Answer 8

Prologue:

A large number of database systems provide something like an Auditing Trail or Log.

Sometimes primitive, sometimes difficult to read, sometimes based on data, sometimes based on command/instructions/scripts.

SQL-server lacked (at least in older standard editions) a feature to access the audit information on both data and scripts.

Why ?

Mike Vessey asked Why? (At least I think he asked why).

My answer to that : I think that there is at least some Auditing trail reading possibility in the latest (or more recent) Enterprise versions of SQLserver.

Would this be sufficient for my situation. At the moment we are looking to change a database from SQLanywhere to SQLserver. SQLanywhere has an Audit trail possibility where scripts can be generated to 'replay' actions. Is something available in SQLserver ?

In :

https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine?view=sql-server-ver15

There is mention of an audit database engine within SQLserver, but I have no experience with this. Building up enough experience with something like an Audit trail takes time and might lead to disappointment.

Epiloge:

My question is, what are the possibilities with SQLserver for auditing? And more specific what is build into the engine.

(Not building your own triggers. Not specifically switching on the profiler.)What can be used to audit trail actions which have resulted into a problem, after the problem has occurred ?

Thanks for your time and attention,

Ben

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 9

if you are looking at data auditing

CDC can be quite cool

https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-data-capture-sql-server?view=sql-server-ver15

it might not be your solution, but give it a look over

MVDBA

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 10

I really do think that you should look at Change data capture (CDC)

https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-data-capture-sql-server?view=sql-server-ver15

MVDBA

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 11

Regardless of which method you plan on, if you intend to audit every data change even in "just" user databases, you should immediately at least double the disk space and all the places you backup data to. Personally, I'd quadruple it. Even just auditing which code made changes is going to produce a hell of a lot of data.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Andrey Right there with Babe Points: 780 More actions · Answer 12

it's worth to check the content of default trace which is enabled by default on all editions of SQL server 2005+

https://www.mssqltips.com/sqlservertip/1739/using-the-default-trace-in-sql-server/

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 13

Andrey wrote:

it's worth to check the content of default trace which is enabled by default on all editions of SQL server 2005+
https://www.mssqltips.com/sqlservertip/1739/using-the-default-trace-in-sql-server/

"It Depends". On my main production server, the default trace recycles so quickly that there's no way to count on it. There's also no way to make it bigger or tell it to use more than the handful of history traces it makes unless you sample it once every minute or so.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Andrey Right there with Babe Points: 780 More actions · Answer 14

Jeff Moden wrote:

Andrey wrote:
it's worth to check the content of default trace which is enabled by default on all editions of SQL server 2005+
https://www.mssqltips.com/sqlservertip/1739/using-the-default-trace-in-sql-server/
"It Depends". On my main production server, the default trace recycles so quickly that there's no way to count on it. There's also no way to make it bigger or tell it to use more than the handful of history traces it makes unless you sample it once every minute or so.

You're right, Jeff, that's why default trace was mentioned as "it's worth to check" but not as "the only source you can rely and trust".

Regards,

Andrey.