October 31, 2014 at 8:50 am
I know the subject isn't the best, but I wanted to give an idea what I'm asking. I'm required to have a server-side trace which records certain trace IDs. In the most recent requirements handed down, they've (finally) acknowledged that Extended Events are replacing Traces (someday.) BUT there's quite a few trace events we need to track which don't have an equivalent XE.
BUT I started poking around and came across Server / Database Audits, which based on this MS article does have equivalent audits for these traces.
So, I've actually got a couple questions about these:
First: Some audit items are listed under both the Server and Database level. If I define an audit on one of these items (lets say the DATABASE_OBJECT_CHANGE_GROUP) at the server level, will it capture such events that occur on the databases on the server, or will I *also* need to define it at the database level for each DB I need to audit (sadly, all of them.)
Second: If I set up the database level audits on Model, would they then be "auto applied" to any new DBs that get created on the server, perhaps only needing to be enabled?
Of course, whether I start switching over to audits instead of traces will depend on how the policy is interprepted by our security folks...
Thanks all,
Jason
October 31, 2014 at 9:11 am
Books Online seems to be quite clear that DATABASE_OBJECT_CHANGE_GROUP applies to any databas, and there is a warning that this can result in large logs.
As for the question of model, I don't know. The audit specifications would surely be copied, but I would not count on that they be active automatically.
In either case, you should absolutely test. And if you audit DATABAS_OBJECT_CHANGE_GROUP, you should particularly check what happens when you create temp tables. Depending on the nature of your applications, this may happen a lot - and you pronbably don't want that to be audited.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
October 31, 2014 at 12:26 pm
Thanks Erland. We're already required to have the equivalent of the DATABASE_OBJECT_CHANGE_GROUP in our trace, so it would also need to be included if we switch over to using Audits.
Thankfully, I don't see a lot of temp tables being used by the applications (so far,) most of the temp tables are created by a few of my scripts I use to keep an eye on things.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply