I’ve seen this question comming back again and again on forums and discussion groups. The ALTER DATABASE … SET ENABLE_BROKER seems to run forever and doesn’t complete. How long should one wait?
This statement completes imeadetly, but the problem is that is requires exclusive access to the database! Any connection that is using this database has a shared lock on it, even when idle, thus blocking the ALTER DATABASE from completing.
There is an easy trick for fix the problem: use the termination options of ALTER DATABASE:
ROLLBACK AFTER integer [ SECONDS ] | ROLLBACK IMMEDIATE | NO_WAIT
-ROLLBACK will close all existing sessions, rolling back any pending transaction.
– NO_WAIT will terminate the ALTER DATABASE statement with error if other connections are blocking it.
ALTER DATABASE [<dbname>] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;
A number of features behave differently when running under activated stored proc. Selecting from server level views, selecting from dynamic management views, using linked servers and other. Executing the same procedure from a user session yields different results. E.g. if the procedures looks up a dynamic management view like sys.dm_…, it gets fewer rows when running under activation than when running from a user session.
What happens is that you overlook the fact that activation is executing under an EXECUTE AS context. The BOL have a great article explaining the behavior of EXECUTE AS, ‘Extending Database Impersonation by Using EXECUTE AS’, http://msdn2.microsoft.com/en-us/library/ms188304.aspx. It is not activation that causes the different behavior, but the fact that activation ALWAYS uses an EXECUTE AS context. The ‘Troubleshooting Activated Stored Procedures’ topic in BOL (http://msdn2.microsoft.com/en-us/library/ms166102(en-US,SQL.90).aspx) actually explains this and recommends that you also use an EXECUTE AS in the user session when invoking the procedure for debugging purposes, to get the same behavior and results as when activated.
A short explanation of the problem is that the activation execution context is trusted only in the database, not in the whole server. Anything related to the whole server, like a server level view or a dynamic management view or a linked server, acts as if you logged in as [Public].
The recommended way to solve the issue is to sign the procedure with a server level certificate that has the proper rights needed for the operation in question (see http://msdn2.microsoft.com/en-us/library/ms181700.aspx). Another possible way to fix the problem is to mark the database as TRUSTWORTHY, using ALTER DATABASE. But you better read the carefully the ‘Extending Database Impersonation by Using EXECUTE AS’ before doing this step and make sure you understand all the implications. You should do this only if you completely trust the dba of the database in question, as he becomes a de facto sysadmin when the database is marked as trustworthy.