Sunday, March 25, 2012

Automatically enable broker after restore or attach

Is there a way to automatically enable Service Broker on SQL Server 2005 Express Edition after a database has been attached or restored?
My initial idea was to check if broker is disabled whenever the client application starts and enable it from there but the problem with this is that the current user might not have the required permissions. So now I'm looking for another way to enable it right after restore/attach if at all possible. Any ideas would be appreciated.

Simplest approach would be to enabled it via a procedure that does have the appropiate permissions (using EXECUTE AS and code signing).

Other possiblity would be to create a server event notification and use an activate procedure in msdb to enable the database:

Code Snippet

use msdb;

go

create queue [dbevents]

create service [dbevents] on queue [dbevents] ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);

create event notification [dbbackuprestore]

on server for AUDIT_BACKUP_RESTORE_EVENT

to service 'dbevents', 'current database';

No comments:

Post a Comment