?
Solved

Microsoft SQL 2005 Auditing feature

Posted on 2011-10-20
13
Medium Priority
?
173 Views
Last Modified: 2012-05-12
"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?"
0
Comment
Question by:Netsol-NOS
  • 4
  • 4
  • 3
  • +1
12 Comments
 
LVL 21

Expert Comment

by:JestersGrind
ID: 36999373
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
 
LVL 15

Expert Comment

by:Anuj
ID: 36999417
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
 
LVL 21

Expert Comment

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

Greg

0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:Netsol-NOS
ID: 36999501
Yes, very right Greg.
0
 

Author Comment

by:Netsol-NOS
ID: 37017409
I've requested that this question be deleted for the following reason:

NO acceptable solution recieved
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 37016715
You could use event notifications...
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 37016838
Let me know if you want me to explain this in more detail or if you want to delete the question.
0
 

Author Comment

by:Netsol-NOS
ID: 37016893
yes sure please explain it- As I have to inform to my data base team.
0
 
LVL 16

Accepted Solution

by:
EvilPostIt earned 2000 total points
ID: 37016983
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
 

Author Comment

by:Netsol-NOS
ID: 37017010
Many thanks great effor you did. let me share it with my Db users and will update you.
0
 
LVL 21

Expert Comment

by:JestersGrind
ID: 37017410
Sometimes, "You can't do that" is the correct answer.

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

Greg

0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 37017623
But this clearly can be dont with either ddl triggers or event notifications...
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question