How to access a changed database within an AUDIT_DATABASE_MANAGEMENT_EVENT?

  • In writing an event processor for handling database management events, I can't seem to see the database that's just been restored, attached, or created.

    All I'd like to do is automate the granting of permissions on the new database - I can't use model permissions because I need to handle restores and attaches as well.

    First I got this working inside a CREATE_DATABASE DDL trigger, but shockingly that didn't get fired when I ran a create ... with attach, and unsurprising it didn't get fired on a restore.

    So now I'm trying to do it in an activation stored procedure on the AUDIT_DATABASE_MANAGEMENT_EVENT, which does get invoked on all types of creates - a basic create, a create ... with attach, and a restore.

    But inside the activation stored procedure, the only databases I seem to have access to are master, tempdb, and the database with the activation procedure itself. All other databases seem to be absent in the security context, even though I'm executing AS SELF under a local admin account.

    I can probably execute an xp_cmdshell and run sqlcmd to grant the permissions, but that seems like a hack.

    Any suggestions? Is there another event driven way to do this?

    Running SQL Server version:

    Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86)

    Mar 23 2007 16:28:52

    Copyright (c) 1988-2005 Microsoft Corporation

    Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

    Sample script follows:

    USE master;

    GO

    IF NOT EXISTS (SELECT * FROM sys.databases

    WHERE name = 'MyAuditTestDB')

    BEGIN

    CREATE DATABASE MyAuditTestDB;

    END;

    GO

    IF NOT EXISTS (SELECT * FROM sys.databases

    WHERE name = 'MyAuditTestDB'

    AND is_broker_enabled = 1)

    BEGIN

    ALTER DATABASE MyAuditTestDB SET ENABLE_BROKER;

    END;

    GO

    USE MyAuditTestDB;

    GO

    IF OBJECT_ID('dbo.tblDBAudit') IS NOT NULL

    BEGIN

    DROP TABLE dbo.tblDBAudit;

    END

    GO

    CREATE TABLE dbo.tblDBAudit (auditEntry xml NOT NULL);

    GO

    IF OBJECT_ID('dbo.tblDBsVisible') IS NOT NULL

    BEGIN

    DROP TABLE dbo.tblDBsVisible;

    END

    GO

    CREATE TABLE dbo.tblDBsVisible (activation_time datetime, name sysname, create_date datetime);

    GO

    IF OBJECT_ID('dbo._sp_DBQActivation') IS NOT NULL

    BEGIN

    DROP PROCEDURE dbo._sp_DBQActivation;

    END

    GO

    CREATE PROCEDURE dbo._sp_DBQActivation

    AS

    BEGIN

    DECLARE @msg_body xml;

    BEGIN TRANSACTION

    BEGIN TRY

    PRINT 'Processing database management audit event...';

    RECEIVE TOP(1)

    @msg_body = CASE

    WHEN validation = 'X' THEN CAST(message_body AS XML)

    ELSE NULL

    END

    FROM dbo.DBQ;

    IF @msg_body IS NOT NULL

    BEGIN

    /* What I want to do is figure out what database was created, then add permissions:

    exec [newdb].dbo.sp_addrolemember 'db_backupoperator', 'User1'

    exec [newdb].dbo.sp_addrolemember 'db_datareader', 'User1'

    exec [newdb].dbo.sp_addrolemember 'db_owner', 'User2'

    but I can't see any databases but master, tempdb, and MyAuditTestDB

    */

    INSERT INTO dbo.tblDBsVisible

    SELECT GETDATE(), name, create_date

    FROM sys.databases;

    INSERT INTO dbo.tblDBAudit SELECT @msg_body;

    END;

    END TRY

    BEGIN CATCH

    PRINT ERROR_MESSAGE();

    END CATCH

    COMMIT TRANSACTION

    END

    GO

    IF EXISTS(SELECT * FROM sys.services WHERE name = 'DBSvc')

    DROP SERVICE DBSvc ;

    GO

    IF OBJECT_ID('DBQ') IS NOT NULL AND

    EXISTS(SELECT * FROM sys.service_queues WHERE name = 'DBQ')

    DROP QUEUE dbo.DBQ;

    GO

    CREATE QUEUE dbo.DBQ

    -- Activation turned on

    WITH STATUS = ON,

    ACTIVATION (

    PROCEDURE_NAME = MyAuditTestDB.dbo._sp_DBQActivation,

    MAX_QUEUE_READERS = 1,

    EXECUTE AS SELF )

    ON [DEFAULT] ;

    GO

    CREATE SERVICE

    DBSvc ON QUEUE dbo.DBQ

    ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);

    IF EXISTS(SELECT * FROM sys.server_event_notifications

    WHERE name = 'EventNotifyDB')

    DROP EVENT NOTIFICATION EventNotifyDB ON SERVER ;

    GO

    CREATE EVENT NOTIFICATION EventNotifyDB

    ON SERVER

    FOR AUDIT_DATABASE_MANAGEMENT_EVENT

    TO SERVICE 'DBSvc', 'current database';

    GO

    /* Now that we're setup, execute a bunch of items that should fire the event */

    CREATE DATABASE TestingAuditDBEvent

    ON

    ( Name = 'TestingAuditDBEventDB', FILENAME = N'C:\TestingAuditDBEvent.mdf' )

    LOG ON

    ( Name = 'TestingAuditDBEventLog', FILENAME = N'C:\TestingAuditDBEvent.LDF' )

    GO

    BACKUP DATABASE TestingAuditDBEvent TO

    DISK = N'C:\TestingAuditDBEvent.bak'

    WITH NOFORMAT, INIT,

    NAME = N'TestingAuditDBEventBackup',

    SKIP, NOREWIND, NOUNLOAD, STATS = 10

    GO

    DROP DATABASE TestingAuditDBEvent

    GO

    RESTORE DATABASE TestingAuditDBEvent FROM

    DISK = N'C:\TestingAuditDBEvent.bak'

    WITH FILE = 1,

    MOVE N'TestingAuditDBEventLog' TO N'C:\TestingAuditDBEvent.LDF',

    NOUNLOAD, STATS = 10

    GO

    EXECUTE sp_detach_db 'TestingAuditDBEvent'

    GO

    CREATE DATABASE TestingAuditDBEvent

    ON

    ( FILENAME = N'C:\TestingAuditDBEvent.mdf' ),

    ( FILENAME = N'C:\TestingAuditDBEvent.LDF' )

    FOR ATTACH

    GO

    DROP DATABASE TestingAuditDBEvent

    GO

    /* Let's find out what we saw inside the event context */

    SELECT * FROM dbo.tblDBsVisible;

    SELECT * FROM dbo.tblDBAudit;

    GO

    /* Cleanup our test environment */

    IF EXISTS(SELECT * FROM sys.server_event_notifications

    WHERE name = 'EventNotifyDB')

    DROP EVENT NOTIFICATION EventNotifyDB ON SERVER ;

    IF EXISTS(SELECT * FROM sys.services WHERE name = 'DBSvc')

    DROP SERVICE DBSvc ;

    IF OBJECT_ID('DBQ') IS NOT NULL AND

    EXISTS(SELECT * FROM sys.service_queues WHERE name = 'DBQ')

    DROP QUEUE dbo.DBQ;

    IF OBJECT_ID('dbo.tblDBAudit') IS NOT NULL

    BEGIN

    DROP TABLE dbo.tblDBAudit;

    END

    IF OBJECT_ID('dbo.tblDBsVisible') IS NOT NULL

    BEGIN

    DROP TABLE dbo.tblDBsVisible;

    END

    IF OBJECT_ID('dbo._sp_DBQActivation') IS NOT NULL

    BEGIN

    DROP PROCEDURE dbo._sp_DBQActivation;

    END

    USE MASTER

    GO

    IF EXISTS (SELECT * FROM sys.databases

    WHERE name = 'MyAuditTestDB')

    BEGIN

    DROP DATABASE MyAuditTestDB;

    END

  • Many thanks to Remus Rusanu and his wonderful service broker blog. Apparently even with execute as self, SQL Server doesn't really trust the stored procedure we're running. The problem is described fully in http://blogs.msdn.com/remusrusanu/archive/2006/01/12/512085.aspx.

    Two solutions. If it's an internal system (not production), or if your dbos are already sysadmins on the server, you can just mark the database as trustworthy - ALTER DATABASE MyAuditTestDB SET TRUSTWORTHY ON;

    That solves my needs because I'm only doing this automatic granting on development servers. If you need a production solution, you can "sign the activation stored procedure" as trusted. Sample code is at http://rusanu.com/2006/03/01/signing-an-activated-procedure/

    Finally, for a description of why you can't use TRUSTWORHTY ON unless you really, really trust the dbo of the database see http://msdn2.microsoft.com/en-us/library/ms188304.aspx.

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

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