October 10, 2011 at 8:52 am
I'd like to add more levels of auditing to your system, but given we're only running Standard Edition we don't have the full gambit of audit tools available to Enterprise users. Most articles though do say there is a subset of auditing tools Standard Edition has, but every article I read only focuses on those available to Enterprise.
Can someone point me to an article or MS document that shows how to setup some basic audits in Standard Edition? I basically want to track any changes to database objects (views, procedures, indexes, tables, etc) and possibly track Selects to certain tables. Using the Developer Edition I see how easily these can be done through Audits, but again since we're on Standard Edition it's not available to us. But what options do we have?
Thanks --
Sam
October 10, 2011 at 9:05 am
Changes to objects, you can audit via DDL triggers. Search "t-sql ddl trigger" and you'll get the articles on that.
Selects, you'll have to build your own audit solution. Traces might do what you need, depending on how connections to the database are managed, but more likely you'll need to use stored procedures for the selects and add an audit portion to them. That's pretty easy to do on a per-proc basis, but can be a lot of work if you have a lot of procs.
Example:
USE DBA; -- I use a database called DBA for my audit logs
GO
CREATE TABLE dbo.SelectAudit (
AuditDate datetime not null default(getdate()),
AuditData XML not null);
GO
create proc dbo.SelectSample
(@Param1_in int,
@Param2_in int,
@UserID_in int int,
@Param3_out int output)
as
set nocount on;
-- Audit
insert into dbo.SelectAudit (AuditData)
select (select 'dbo.SelectSample' as SelectProc, @Param1_in as Param1, @Param2_in as Param2, @UserID_in as UserID for XML RAW, type);
select MyColumn, MyOtherColumn
from dbo.MyTable
where Col1 = @Param1_in
and Col2 = @Param2_in;
select @Param3_out = @@rowcount; -- only included to show difference in audit for input vs output params
That would be one way to do it. It records the object called, the input parameter values, and the ID number of the user who called it. If the connection defines the user (not usual in web applications), you could skip this and use a trace instead, which would be better for overall performance.
The XML nature of the audit log allows for procedures with a variety of parameter definitions to all use the same audit log, which is lazy, effective, and efficient in terms of refactoring or building new procs.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 10, 2011 at 9:51 am
Thanks... unfortunately the application we're developing uses Views when selecting data from the database instead of Procedures so I can't add auditing at that level. I just setup a Trace to at least monitor when objects are dropped, created, and altered (Events 128, 130-135) so that will help to some degree. Other than that I guess I'll have to create Select Triggers on tables needing that level of auditing.
It just really confuses me why Microsoft didn't make the Auditing features available in all editions of MS SQL since no matter how large or small the shop Auditing is a must and best practice.
But thanks for the suggestions, and take care --
Sam
October 10, 2011 at 11:07 am
No such thing as a "Select Trigger" in SQL Server.
And they didn't include it specifically to force people who want it to buy the more expensive product. That and a dozen other Enterprise-Only features.
You can run a trace for Selects. RPC complete events can capture that. Won't tell you "who" ran the Select, but will tell you what the query was. Traces can only capture "who" if the connection is individual.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply