locked
2008 R2 Audit Problem - DATABASE_OBJECT_PERMISSION_CHANGE_GROUP RRS feed

  • Question

  • I've create an audit on one of our SQL Servers. I've tested that each of the audit groups is working. I have a problem in that the audit doesn't capture anything from the DATABASE_OBJECT_PERMISSION_CHANGE_GROUP.

    Does anyone have any ideas please? The code I've use to create the audit and test that group is below:

    USE [master]
    GO
    
    CREATE SERVER AUDIT [SQLObjectChangeAudit]
    TO FILE 
    (	FILEPATH = N'S:\'
    	,MAXSIZE = 1024 MB
    	,MAX_ROLLOVER_FILES = 2147483647
    	,RESERVE_DISK_SPACE = OFF
    )
    WITH
    (	QUEUE_DELAY = 1000
    	,ON_FAILURE = CONTINUE
    	,AUDIT_GUID = 'e2ec475e-d270-4671-b230-1d67a343a13c'
    )
    GO
    
    CREATE SERVER AUDIT SPECIFICATION [AuditSpec_SQLObjectChange]
    FOR SERVER AUDIT [SQLObjectChangeAudit]
    ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
    ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
    ADD (DATABASE_PERMISSION_CHANGE_GROUP),
    ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP),
    ADD (SERVER_OBJECT_PERMISSION_CHANGE_GROUP),
    ADD (SERVER_PERMISSION_CHANGE_GROUP),
    ADD (DATABASE_OBJECT_CHANGE_GROUP),
    ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),
    ADD (SERVER_OBJECT_CHANGE_GROUP)
    WITH (STATE = ON)
    GO
    
    --Enabled the Audit through Management Studio
    --then...
    
    USE ChangeControl --The name of the database I'm testing
    GO
    GRANT SELECT ON dbo.adhoc TO ChangeControlUser --grant a user select to a table
    GO
    
    SELECT	a.event_time,
    		x.[name] As Action,
    		a.[session_server_principal_name],
    		a.database_name,
    		a.[schema_name],
    		a.[object_name],
    		a.[statement],
    		a.succeeded  
    FROM	sys.fn_get_audit_file ('\\ServerName\S$\SQLObjectChangeAudit_*.sqlaudit',default,default) a INNER JOIN
    		(SELECT DISTINCT action_id, [name] FROM Sys.dm_audit_actions) x ON a.action_id = x.action_id 
    

     

    Wednesday, November 30, 2011 6:34 PM

Answers

  • Deleted
    • Marked as answer by JohnGSXRuk Thursday, December 1, 2011 11:41 AM
    Wednesday, November 30, 2011 9:36 PM
  • Shawn's first reply is correct. Server audit specifications only capture server level events. You need to create a database audit specification to capture database level events. A server audit specification does not act like a database audit specification in all databases.

     

    See: http://technet.microsoft.com/en-us/library/cc280386(SQL.100).aspx

     



    My blog: SQL Soldier
    Twitter: @SQLSoldier
    Microsoft Certified Master: SQL Server 2008
    My book: Pro SQL Server 2008 Mirroring
    • Marked as answer by JohnGSXRuk Thursday, December 1, 2011 11:43 AM
    Thursday, December 1, 2011 3:20 AM
  • Your reading of the BOL is correct. Here's a script that exercises DATABASE_OBJECT_PERMISSION_CHANGE_GROUP from a SERVER AUDIT SPECIFICATION.

    Cheers, Bob

    use master
    go

    create server audit foo to file (filepath = 'c:\temp')

    alter server audit foo with (state = on)

    create server audit specification bar
    for server audit foo
    add (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP)

    alter server audit specification bar with (state = on)
    go

    use test -- you can use any database with same results
    go

    create user bob without login
    go

    create schema myschema authorization dbo
    go

    -- this generates an audit record
    grant execute on schema::myschema to bob
    go

    create assembly compatsort from 'c:\temp\compatsortex.dll'
    go

    -- so does this
    grant references on assembly::compatsort to bob
    go

     

    • Marked as answer by JohnGSXRuk Thursday, December 1, 2011 11:42 AM
    Thursday, December 1, 2011 9:10 AM

All replies

  • Cheers for the reply Shawn.

    I read that article earlier today and just had a look through it again. From my understanding implementing on a server level audit would have the same effect as implementing this audit group at each database level (to negate this need).

    I might have missed some bit of text that explains it but it just seems odd that they would have this audit group in the list for server level if it wasn't meant to be used there. 

    Kind Regards

    John Sims

    Wednesday, November 30, 2011 11:44 PM
  • As a point of reference under the Server level Audit section of the page it reads for DATABASE_OBJECT_PERMISSION_CHANGE_GROUP:

    "This event is raised when a GRANT, REVOKE, or DENY has been issued for database objects, such as assemblies and schemas. This event is raised for any object permission change for any database on the server."

    This kind of reads that it should work.

    Kind Regards

    John Sims

    Thursday, December 1, 2011 12:03 AM
  • Shawn's first reply is correct. Server audit specifications only capture server level events. You need to create a database audit specification to capture database level events. A server audit specification does not act like a database audit specification in all databases.

     

    See: http://technet.microsoft.com/en-us/library/cc280386(SQL.100).aspx

     



    My blog: SQL Soldier
    Twitter: @SQLSoldier
    Microsoft Certified Master: SQL Server 2008
    My book: Pro SQL Server 2008 Mirroring
    • Marked as answer by JohnGSXRuk Thursday, December 1, 2011 11:43 AM
    Thursday, December 1, 2011 3:20 AM
  • Your reading of the BOL is correct. Here's a script that exercises DATABASE_OBJECT_PERMISSION_CHANGE_GROUP from a SERVER AUDIT SPECIFICATION.

    Cheers, Bob

    use master
    go

    create server audit foo to file (filepath = 'c:\temp')

    alter server audit foo with (state = on)

    create server audit specification bar
    for server audit foo
    add (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP)

    alter server audit specification bar with (state = on)
    go

    use test -- you can use any database with same results
    go

    create user bob without login
    go

    create schema myschema authorization dbo
    go

    -- this generates an audit record
    grant execute on schema::myschema to bob
    go

    create assembly compatsort from 'c:\temp\compatsortex.dll'
    go

    -- so does this
    grant references on assembly::compatsort to bob
    go

     

    • Marked as answer by JohnGSXRuk Thursday, December 1, 2011 11:42 AM
    Thursday, December 1, 2011 9:10 AM
  • All - thanks for your help on this one - much appreciated :-)

    Thursday, December 1, 2011 11:44 AM