March 12, 2008 at 10:15 am
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
March 17, 2008 at 5:10 am
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