December 13, 2011 at 11:22 pm
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
December 13, 2011 at 11:25 pm
Could you please elaborate your requirements ? What all the data you want to collect in your audit ? What are your plans & your thoughts ?
December 13, 2011 at 11:56 pm
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
December 14, 2011 at 12:04 am
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.
December 14, 2011 at 12:12 am
Dev has given you the answer 🙂
December 14, 2011 at 12:17 am
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
December 14, 2011 at 12:21 am
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?
December 14, 2011 at 12:45 am
Hi,
DEV team will provide the script to DBA deploy in Production .
December 14, 2011 at 1:03 am
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.
December 14, 2011 at 1:46 am
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://www.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://www.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
December 14, 2011 at 2:06 am
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).
December 14, 2011 at 2:27 am
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
December 14, 2011 at 2:44 am
Hi Gail Shaw,
Thanks a lot ,Thanks a lot ,Thanks a lot
I will work on this.
Thanks a lot again for quick reply
December 14, 2011 at 4:25 am
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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 14, 2011 at 8:32 am
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
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply