DATABASE_OBJECT_CHANGE_GROUP audit group does not audit drop proc by SQLWaldorf

Closed

as By Design Help for as By Design

Type:	Bug
ID:	370103
Opened:	9/26/2008 6:25:22 AM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

When I activate a database audit specification with the DATABASE_OBJECT_CHANGE_GROUP
selected, and I create and drop a stored proc, only the create is in the audit log, but not the drop.

Details (expand)

English

SQL Server 2008 - Developer Edition

SQL Engine

Windows Vista SP1

US English

CREATE SERVER AUDIT [Audit-20080926-140551]
TO FILE
(    FILEPATH = N'C:\'
    ,MAXSIZE = 10 MB
    ,MAX_ROLLOVER_FILES = 20
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(    QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
)

-- enable server audit in management studio object explorer
USE AdventureWorks
GO
CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-20080926-144412]
FOR SERVER AUDIT [Audit-20080926-140551]
ADD (DATABASE_OBJECT_CHANGE_GROUP)
GO
-- enable database audit specification in management studio object explorer
USE AdventureWorks
GO
CREATE PROC testing AS SELECT 'Test, 1,2,3, test...'
GO
EXEC testing
GO
DROP PROC testing
GO

only create proc in audit file

both create proc and drop proc in audit file

0 attachments

Posted by William Soranno on 12/7/2011 at 8:16 AM

If this is working by design, then the documentation needs to be updated. MSDN article, http://msdn.microsoft.com/en-us/library/cc280663.aspx, states that DROP's are logged for the DATABASE_OBJECT_CHANGE_GROUP.

Posted by Microsoft on 10/10/2008 at 4:14 PM

Hi,

Thanks for your feedback. The behaviour you're seeing is by-design. In order to audit CREATE/DROP of an SP, you need to add the SCHEMA_OBJECT_CHANGE_GROUP to your audit specification. The DATABASE_OBJECT_CHANGE_GROUP is actually auditing the ALTER permission check on the SCHEMA as part of the CREATE statement.

Il-Sung.

Microsoft