Post reply

SQL Audit - DML action type for database role

  • September 28, 2012 at 12:16 pm

    #262377

    Hi,

    I am having trouble setting up SQL Audit. Specifically, I want to audit DMLs for everyone in the db_owner or one of my custom database roles.

    Audit Action Types SELECT, UPDATE, etc are properly logged for

    CREATE DATABASE AUDIT SPECIFICATION [Audit_Db_Dbo]

    FOR SERVER AUDIT [Audit_Dbo]

    ADD (SELECT ON OBJECT::[dbo].[SecDetail] BY [public])

    WITH (STATE = ON)

    But not for db_owner or any of my custom roles.

    ADD (SELECT ON OBJECT::[dbo].[SecDetail] BY [db_owner])

    What am I missing?

    Thanks.

  • September 28, 2012 at 1:20 pm

    #1543601

    Try:

    ADD (SELECT ON OBJECT::[dbo].[SecDetail] BY [dbo])

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • September 28, 2012 at 1:30 pm

    #1543606

    Yes, public and dbo work. But I want to set it up for only a certain group of users with elevated privileges to identify inappropriate access.

  • September 28, 2012 at 1:58 pm

    #1543618

    You are exactly right. That works. Thanks.

Viewing 4 posts - 1 through 4 (of 4 total)

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