DBA activity How to find out backend correction audit log

Question

DBA activity How to find out backend correction audit log

jamessdba

SSCrazy

Points: 2254
More actions
December 13, 2011 at 11:22 pm

#261595

HI,
As per our policy production support process we need to monitor/collect audit log for back end correction
Please suggest how do i monitor/collect audit log for the perticular user
Thanks in advance

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

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

Divine Flame SSCoach Points: 15943 More actions · Answer 1

Could you please elaborate your requirements ? What all the data you want to collect in your audit ? What are your plans & your thoughts ?

Sujeet Singh

jamessdba SSCrazy Points: 2254 More actions · Answer 2

Hi,

Our requirement is

when ever any backend correction or applying patches(scripts) done @production SQlserver 2005 by our user (Ex : changes in objects(tables,proc,func,views,...)

how do i capture the auditlog for any changes in the Produciton DBase

Pls advice

Thanks in Advance

Devendrakumar Shirbad SSC-Forever Points: 42493 More actions · Answer 3

jamessdba (12/13/2011)
Hi,
Our requirement is
when ever any backend correction or applying patches(scripts) done @production SQlserver 2005 by our user (Ex : changes in objects(tables,proc,func,views,...)
how do i capture the auditlog for any changes in the Produciton DBase
Pls advice
Thanks in Advance

In short, you want to monitor anything & everything. It’s possible on the cost of performance.

OR

You have already identified the areas e.g. patches, add audit logic in patch itself. You can also Baseline the PROD server schema & log changes in version control tools.

Divine Flame SSCoach Points: 15943 More actions · Answer 4

Divine Flame

SSCoach

Points: 15943

December 14, 2011 at 12:12 am

#1421034

Dev has given you the answer 🙂

Sujeet Singh

jamessdba SSCrazy Points: 2254 More actions · Answer 5

Hi,

Thanks for the Reply

My requirement As A DBA i need to capture back end correction(done by DEV team) audit log report to send my Manager

Devendrakumar Shirbad SSC-Forever Points: 42493 More actions · Answer 6

jamessdba (12/14/2011)
Hi,
Thanks for the Reply
My requirement As A DBA i need to capture back end correction(done by DEV team) audit log report to send my Manager

Fine. I have one question here. Who deploy the code DBA or DEV team?

jamessdba SSCrazy Points: 2254 More actions · Answer 7

Hi,

DEV team will provide the script to DBA deploy in Production .

Devendrakumar Shirbad SSC-Forever Points: 42493 More actions · Answer 8

Good. So you (as a DBA) have control here. Keep checking in these scripts in Version Control tools as & when they arrive. Include this task in your DB Deployment Process Document as well.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 9

Very common requirement. Two ways you can do this.

1) Default trace. All object alterations are logged in the default trace, so you can read that from a scheduled job and log the details. Default trace: http://qa.sqlservercentral.com/articles/SQL+Server+2005/64547/. If you do that, be careful. The trace rolls over after a file reaches 20MB or the server restarts and it only keeps 5 files. I've seen places where the default trace only goes back 5 hours. (of course, you can roll your won permanent trace too)

2) DDL triggers. Triggers that fire, not for data changes, but for structure changes. There are a whole load of possible events they fire for. If you want all DDL changes in a database, then create your trigger for DDL_DATABASE_LEVEL_EVENTS. Be sure to filter out statistics events, they'll flood your logs.

This article is pretty old, but it's got the basics: http://qa.sqlservercentral.com/articles/Security/2927/

Here's a very simplistic DDL trigger:

CREATE TRIGGER [trg_AuditSchemaChanges]

ON DATABASE

FOR DDL_DATABASE_LEVEL_EVENTS

AS

SET ANSI_PADDING ON

SET NOCOUNT ON

DECLARE @data XML

SET @data = EVENTDATA()

IF @data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)') NOT LIKE '%statistics%'

INSERT Testing.dbo.AuditSchemaChanges (DatabaseName, ObjectName, ObjectType, EventDate, EventType, UserName,FullCommand)

VALUES (DB_Name(),

@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(128)'),

@data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(50)'),

GetDate(),

@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(100)'),

original_login(),

@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)')

)

Also note that disabling or enabling the trigger does not fire the trigger, so you can still have someone making unauthorised changes by disabling the trigger first, making the changes, then re-enabling the trigger. But that requires malicious intent, it's not something that could be done by accident.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Devendrakumar Shirbad SSC-Forever Points: 42493 More actions · Answer 10

Gail has covered pretty much everything. Just highlighting few points on her post as caution (if you quick read)

•The trace rolls over after a file reaches 20MB or the server restarts and it only keeps 5 files.

•DDL triggers. Triggers that fire, not for data changes, but for structure changes.

•Triggers are costly affairs (in terms of performance).

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 11

Dev (12/14/2011)
Triggers are costly affairs (in terms of performance).

A well-written DDL trigger is not a performance problem, unless the DB is creating and tens or hundreds of objects/sec. That's certainly not a "point from my post".

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jamessdba SSCrazy Points: 2254 More actions · Answer 12

Hi Gail Shaw,

Thanks a lot ,Thanks a lot ,Thanks a lot

I will work on this.

Thanks a lot again for quick reply

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 13

If you do need to set up a custom set of monitors to keep track of changes long term, instead of relying on the default trace or using your own trace, I'd strongly suggest setting up an extended events session. They are much more light-weight when it comes to resource usage and more flexible in terms of filtering and controlling their behavior. Jonathan Kehayias has some great articles that can get you started.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 14

Grant Fritchey (12/14/2011)
If you do need to set up a custom set of monitors to keep track of changes long term, instead of relying on the default trace or using your own trace, I'd strongly suggest setting up an extended events session.

James, if you're actually on SQL 2008 then consider Extended Events instead of rolling your own trace if you decide to go that route (as opposed to DDL triggers or the default trace), but if you're on SQL 2005 then Extended events aren't an option, they're SQL 2008 and above.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass