How to access dbs in AUDIT_DATABASE_MANAGEMENT sp? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Service Broker (2005)

How to access dbs in AUDIT_DATABASE_MANAGEMENT sp?

Author

Topic

jteeter
Starting Member

4 Posts

Posted - 2008-03-14 : 06:39:33

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 for 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 for 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

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-03-14 : 22:53:59

Ensure your account has db creation permission.

jteeter
Starting Member

4 Posts

Posted - 2008-03-15 : 02:26:55

Rmiao, thanks for the advice. My account does have db create privileges - it's a local admin on the machine... The dbs get created, I just can't see them from the activation procedure.

Were you able to see other databases running the above script? Did it return anything other than the tempdb, master, and the test db? In mine I can't even see model or msdb from the query, let alone the databases that cause the events to be fired.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-03-15 : 16:03:07

Tried connect to sql with sa then run the script?

jteeter
Starting Member

4 Posts

Posted - 2008-03-17 : 06:48:22

I was/am connected as a local admin, which acts as a sa on the database instance. The script runs without errors, but just doesn't get access to any other databases.

jteeter
Starting Member

4 Posts

Posted - 2008-03-17 : 07:07:05

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 database, 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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources