SQL Server Performance

Monitoring DDL Statements(urgent requirement)

Discussion in 'SQL Server Log Shipping' started by dmaddhali, Nov 30, 2005.

  1. dmaddhali New Member

    I would like to monitor all the DDL Statements which are executed .so that administrator can track all the illegal modifications done to the database. Please help me finding a programatical way to know which DDL statements are getting executed . Is there any stored procedure to know this.we have to meet this requirement using VC++.

    I am planning to
    Method 1:
    1) create an profiler event and
    2) filter it to access SQL::batchcompleted category class.
    3)log information in a file
    4)parse the information in the log for getting the DDL Statements executed.

    Method 2:
    create a automatic stored procedure which would log information to a file
    when create , alter ,drop commands are executed .

    Please help me out regarding this . Thanks in advance.


  2. SQLDBcontrol New Member

    This doesn't seem like a log shipping issue but anyway. Here goes:

    These are your options, in order of preference:

    1) Don't let people issue DDL statements by correctly implementing a secure security model. If you want to track illegal modifications then make them illegal by preventing them in the first place and then you won't need to track anything.


    Running a profiler trace continuously to monitor DDL statements is about your only other way of doing it but it really is NOT a good idea - seriously.





    quote:Originally posted by dmaddhali

    I would like to monitor all the DDL Statements which are executed .so that administrator can track all the illegal modifications done to the database. Please help me finding a programatical way to know which DDL statements are getting executed . Is there any stored procedure to know this.we have to meet this requirement using VC++.

    I am planning to
    Method 1:
    1) create an profiler event and
    2) filter it to access SQL::batchcompleted category class.
    3)log information in a file
    4)parse the information in the log for getting the DDL Statements executed.

    Method 2:
    create a automatic stored procedure which would log information to a file
    when create , alter ,drop commands are executed .

    Please help me out regarding this . Thanks in advance.




    Karl Grambow

    www.sqldbcontrol.com
  3. dmaddhali New Member

    we are using a XYZ product which may be or may not be in maintanance mode.
    During the no maintanance mode of XYZ product some users who have permission may change the sql server database.
    I need to monitor the sql server for any DDL statements and generate a report . The issue is not about illegal access by a person who is not authourized.
  4. dmaddhali New Member

    Can any one please tell me which table/file is logged if any DDL Statements are executed.
  5. SQLDBcontrol New Member

    You'll cause yourself a serious headache (performance and otherwise) if you start using profiler but if that's the way you want to go:

    There is no table or file that is logged when a DDL statement is executed. There is the sysobjects table which will tell you when an object was created.

    If you do use profiler.

    With profiler you can choose (or create) the table/file you want to log into. Then you must use the filter to specifiy which database and, if necessary, which objects you want to trace. In fact, you can even filter on the text data so you can try and filter on DDL statements.


    Hope that helps.


    quote:Originally posted by dmaddhali

    Can any one please tell me which table/file is logged if any DDL Statements are executed.

    Karl Grambow

    www.sqldbcontrol.com
  6. SQLDBcontrol New Member

    You could always buy SQL Server 2005 and use it's DDL Trigger capability.


    quote:Originally posted by dmaddhali

    Can any one please tell me which table/file is logged if any DDL Statements are executed.

    Karl Grambow

    www.sqldbcontrol.com
  7. dmaddhali New Member

    Thanks.Do you want me to use Automatic Triggers which would log the executed DDL statments information ?.

  8. SQLDBcontrol New Member

    There is no such thing as an automatic trigger (unless I've misunderstood you). The only way you can log DDL modifications is if you have SQL Server 2005. In which case you would need to write your own triggers and you would need to create the necessary audit tables. I haven't worked with this feature of SQL Server 2005 so I'd suggest you download an eval edition of SQL Server and test it.

    Come to think of it. There is C2 auditing in SQL Server 2000, which might log DDL. I've never used C2 level auditing in SQL Server 2000 though. Perhaps someone else can confirm if it logs DDL statements? Of have a look around the web for SQL Server and C2 auditing - there's bound to be a paper on it on the Microsoft site.

    Karl Grambow

    www.sqldbcontrol.com
  9. dmaddhali New Member


    Thanks . I am getting a copy of SQL SERVER2005.
    I shall use triggers for ddl statements.
    Meanwhile I created an Event and set the event to get SQL::batchcompleted related log.
    Then I changed the status of event to start mode.
    I have used sp_trace_create, sp_trace_setevent, sp_trace_setstatus events to do these tasks.

    After doing this I entered some queries to create and drop some tables.
    These information is not getting populated in the log (.trc) files.


    This is the code I have used in query analyser to generate log.

    Create a trace which logs the information in satishlog.trc

    1)DECLARE @TraceIdOut int

    exec sp_trace_create @traceid= @TraceIdOut OUTPUT
    ,@options = 2 ,@tracefile =N'c:satishlog'

    PRINT @TraceIdOut


    2)Description :After the first statment is executed it returns the TraceId. This traceid must be used in the second command to set the event.


    DECLARE @On bit

    SET @On = 1

    exec sp_trace_setevent @traceid = 1,@eventid = 12,@columnid =1 , @on = @On



    3)use the trace id returned from command 1 instead of 1 in the below command.
    EXEC sp_trace_setstatus @traceid = 1
    , @status = 1



  10. dmaddhali New Member

    I have done this succesfully by using DDL TRIGGERS in SQL SERVER 2005

Share This Page