Microsoft SQL 2005 Auditing feature

"How can we know the user name, machine name and the executed statements (Specifically enabling and disabling of Triggers) from the database server over which we do not have any explicit tracking utility, like SQL Profiler, running to track the exact activity. Is there a way to know who enabled and/or disabled the triggers and from which machine from the System tables somewhere or by any other mean?"
Netsol-NOSAsked:
Who is Participating?
 
EvilPostItCommented:
I really hate it when i hit backspace and loose the long description i spent 10 minutes writting!!!!

This will create your event notification which will log the xml eventdata to a table called LoggedEvents.

CREATE DATABASE NotificationEvents
GO
ALTER DATABASE NotificationEvents
SET ENABLE_BROKER
GO
CREATE TABLE LoggedEvents(ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,EVENTDATA_XML XML)

CREATE QUEUE NotifyQueue
GO
CREATE SERVICE NotifyService
ON QUEUE NotifyQueue ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
GO

CREATE PROCEDURE [dbo].[usp_EventLogger]
AS 
SET NOCOUNT ON

DECLARE     @message_body XML,
            @message_type_name VARCHAR(256),
            @dialog UNIQUEIDENTIFIER,
            @ident UNIQUEIDENTIFIER
            
WHILE (1 = 1)
BEGIN
    BEGIN TRANSACTION ;
    WAITFOR (
        RECEIVE TOP(1) -- just handle one message at a time
            @message_type_name=message_type_name,  --the type of message received
            @message_body=message_body,      -- the message contents
            @dialog = conversation_handle    -- the identifier of the dialog this message was received on
            FROM NotifyQueue_UserLogout
    ), TIMEOUT 2000 ; -- if the queue is empty for two seconds, give up and go away
    
    IF (@@ROWCOUNT = 0) 
        BEGIN
            ROLLBACK TRANSACTION ;
            BREAK ;
        END ;
        
    IF (@message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
    BEGIN
        PRINT 'End Dialog received for dialog # ' + cast(@dialog as VARCHAR(40)) ;
        END CONVERSATION @dialog ;
    END ;
    ELSE
    BEGIN
		INSERT INTO LoggedEvents VALUES (@message_body)	
	END
	COMMIT TRANSACTION
END
GO

ALTER QUEUE [dbo].[NotifyQueue]
    WITH ACTIVATION (
        STATUS = ON,
        PROCEDURE_NAME = [dbo].[usp_EventLogger] ,
        MAX_QUEUE_READERS = 2,
        EXECUTE AS SELF
    );
GO

CREATE EVENT NOTIFICATION Notify
ON SERVER
FOR AUDIT_SCHEMA_OBJECT_MANAGEMENT_EVENT
TO SERVICE 'NotifyService','current database'

Open in new window

0
 
JestersGrindCommented:
By default, SQL Server doesn't track that sort of information.  You would either need to have profiler running or maybe create a DDL trigger to capture those types of statements.

Greg

0
 
AnujSQL Server DBACommented:
Still DDL triggers wont audit or capture the select statements. The only option on SQL Server 2005 to capture DML and SELECT staement is by SQL Server Profiler. Using SQL Server profiler for auditing is not recommended because being a resource consuming one.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
JestersGrindCommented:
That's right, but he was looking at who disabled and re-enabled some triggers.  Not for select statements.

Greg

0
 
Netsol-NOSAuthor Commented:
Yes, very right Greg.
0
 
Netsol-NOSAuthor Commented:
I've requested that this question be deleted for the following reason:

NO acceptable solution recieved
0
 
EvilPostItCommented:
You could use event notifications...
0
 
EvilPostItCommented:
Let me know if you want me to explain this in more detail or if you want to delete the question.
0
 
Netsol-NOSAuthor Commented:
yes sure please explain it- As I have to inform to my data base team.
0
 
Netsol-NOSAuthor Commented:
Many thanks great effor you did. let me share it with my Db users and will update you.
0
 
JestersGrindCommented:
Sometimes, "You can't do that" is the correct answer.

http://www.experts-exchange.com/help.jsp#hs=29&hi=405

Greg

0
 
EvilPostItCommented:
But this clearly can be dont with either ddl triggers or event notifications...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.