Post reply

DATABASE AUDIT SPECIFICATION

  • February 15, 2022 at 12:23 pm

    #3983826

    Hi, I am trying to create Database Audit Specifications through TSQL commands by remotely connecting to SQL server, but do we have an option to configure SELECT,UPDATE,DELETE,INSERT,EXECUTE (audit action types) on all DBs, OBJECTS, SCHEMAS BY PUBLIC instead of executing thousands of individual statements ? Can someone please advise

  • February 16, 2022 at 1:10 pm

    #3984324

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • February 16, 2022 at 3:25 pm

    #3984404

    You would have to dynamically build the SQL for this and execute on a database by database basis.

    There is nothing server scoped which will monitor those database actions for you.

    You would need to use something like the undocumented feature sp_msg preach db or write something to loop around sys.databases running the create specification queries.

  • February 17, 2022 at 2:54 am

    #3984699

    Hi @SSC Guru @Ant Green, Thanks for your response. Could you please help clarify the below query on this,

    We are trying to create database audit specification for DML statements like SELECT, UPDATE, INSERT, DELETE, EXECUTE, RECEIVE, REFERENCE. In this case, if we audit these statements on database level, will it cover all the objects and schemas from that database as well as wider scope (Red color box) or we still need to individually specify all the OBJECT and SCHEMAS from that database one by one in rows (Yellow color Box).

    The OBJECT selected above screenshot (Yellow color box) is within the same database selected above (Red Color Box), please refer below screenshot for reference.

     

    • This reply was modified 2 years, 9 months ago by  gowthamsm. Reason: added Ant Green name

  • February 17, 2022 at 9:13 am

    #3984817

    You must specify a object name and a principal name when using the object class of database.

     

    eg

    Audit Action Type = Select

    Object Class = Database

    Object Schema = “blank”

    Object Name = AdventureWorks

    Object Principal = Public

  • February 17, 2022 at 1:22 pm

    #3984890

    Yes, I understood that I need to specify object name as database name and object class as database. But, when I specify the object class as Database, will the audit covers all objects (like SPs, Views, Tables etc..) and schemas (like dbo, INFORMATION_SCHEMA) within the database also ?

  • February 17, 2022 at 3:42 pm

    #3984974

    It will monitor everything at the database scope if you select the database object including information_schema.

    You can verify this by creating the audit for your login and verify the audit data.

Viewing 7 posts - 1 through 6 (of 6 total)

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