This is derived from the original post.
However, the original post has two issues.
Both of these issues are related to the SqlDependencySchemaOwner user it creates in the database.
This user owns and executes the stored procedures which SqlDependency creates dynamically.
Microsoft archived that forum and even the original author cannot update the post. The original author has reposted this to stackoverflow.
You can see the improved version of the script below.
The first issue is that SqlDependencySchemaOwner’s default database schema is not defined.
The default schema of the user is dbo.
This schema is used when resolving unqualified identifiers.
If you haven’t noticed, the stored procedure created by SqlDependency contains a lot of unqualified identifier references.
Let us show an actual example with indentation and comments pulled from the C# source into the SQL added for readability:
CREATE PROCEDURE [SqlQueryNotificationStoredProcedure-25fcb454-7c62-4eae-bd41-952277c603fe] AS BEGIN
BEGIN TRANSACTION;
RECEIVE TOP(0) conversation_handle FROM [SqlQueryNotificationService-25fcb454-7c62-4eae-bd41-952277c603fe];
IF (SELECT COUNT(*) FROM [SqlQueryNotificationService-25fcb454-7c62-4eae-bd41-952277c603fe] WHERE message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/DialogTimer') > 0 BEGIN
if ((SELECT COUNT(*) FROM sys.services WHERE name = 'SqlQueryNotificationService-25fcb454-7c62-4eae-bd41-952277c603fe') > 0)
DROP SERVICE [SqlQueryNotificationService-25fcb454-7c62-4eae-bd41-952277c603fe];
if (OBJECT_ID('SqlQueryNotificationService-25fcb454-7c62-4eae-bd41-952277c603fe', 'SQ') IS NOT NULL)
DROP QUEUE [SqlQueryNotificationService-25fcb454-7c62-4eae-bd41-952277c603fe];
-- Don't need conditional because this is self
DROP PROCEDURE [SqlQueryNotificationStoredProcedure-25fcb454-7c62-4eae-bd41-952277c603fe];
END
COMMIT TRANSACTION;
END
Now, when the procedure is created by SqlNotificationUser, whose default schema is SqlDependency, the fully qualified name of the procedure ends up being SqlDependency."SqlQueryNotificationStoredProcedure-25fcb454-7c62-4eae-bd41-952277c603fe".
Because the QUEUE object referenced by the RECEIVE statement already exists, the reference to the queue is resolve as the procedure is defined, allowing it to be found within the SqlDependency schema.
However, because the procedure does not exist yet, the procedure referenced by DROP PROCEDURE uses Deferred Name Resolution.
This means that it is not resolved in the context of the caller who is running CREATE PROCEDURE—instead it will be resolved by the user the procedure executes as.
Because they are VIEW queries and dynamic functions, the OBJECT_ID() check will also run and resolve objects in the context of the user the procedure executes as.
As stated above, the user these are being executed by is SqlDependencySchemaOwner which has a default schema of dbo.
As a result, when the cleanup clause runs, you will see an error like this in your Event Viewer logs and the procedure will stick around even though the system’s cleanup mechanism was triggered:
The activated proc '[SqlDependency].[SqlQueryNotificationStoredProcedure-25fcb454-7c62-4eae-bd41-952277c603fe]' running on queue 'dcxMain40test.SqlDependency.SqlQueryNotificationService-25fcb454-7c62-4eae-bd41-952277c603fe' output the following: 'Cannot drop the procedure 'SqlQueryNotificationStoredProcedure-25fcb454-7c62-4eae-bd41-952277c603fe', because it does not exist or you do not have permission.'
This is because, even though the error message itself proves that the procedure exists, the DROP PROCEDURE is effectively searching for and not finding dbo."SqlQueryNotificationStoredProcedure-25fcb454-7c62-4eae-bd41-952277c603fe" which, indeed, does not exist.
This can be addressed by setting the default schema of the SqlDependencySchemaOwner user.
This also fixes the OBJECT_ID() check.
ALTER USER SqlDependencySchemaOwner WITH DEFAULT_SCHEMA = SqlDependency;
The SqlDependencySchemaOwner user which executes the cleanup procedure has sufficient permission to remove both the QUEUE object and the PROCEDURE object because these objects are both in the SqlDependency schema which that user owns.
However, the SERVICE object is a database-wide object and cannot be associated with a schema.
The creator of the SERVICE, SqlDependencyUser in this case, can remove it because it is the owner.
However, the cleanup sproc needs to be able to both see and remove it to complete its cleanup steps.
With the permissions granted by the original instructions, SqlDependencySchemaOwner cannot see any services in sys.services.
Thus, it won’t even try to remove the service.
This then results in an error when the cleanup procedure tries to clean up the queue:
The activated proc '[SqlDependency].[SqlQueryNotificationStoredProcedure-25fcb454-7c62-4eae-bd41-952277c603fe]' running on queue 'dcxMain40test.SqlDependency.SqlQueryNotificationService-25fcb454-7c62-4eae-bd41-952277c603fe' output the following: 'The queue 'SqlQueryNotificationService-25fcb454-7c62-4eae-bd41-952277c603fe' cannot be dropped because it is bound to one or more service.'
To address this, the SqlDependencySchemaOwner simply needs to be given permission to alter any services in the database.
GRANT ALTER ANY SERVICE TO SqlDependencySchemaOwner;
When combined with the original post, a more complete listing of the permissions required to use SqlDependency is described by the following script:
USE YourDatabase;
GO
--create user for schema ownership
CREATE USER SqlDependencySchemaOwner WITHOUT LOGIN;
GO
--create schema for SqlDependency ojbects
CREATE SCHEMA SqlDependency AUTHORIZATION SqlDependencySchemaOwner;
GO
--set the default schema of the user which will execute the cleanup sproc so that it can resolve objects created by SqlNotificationUser
ALTER USER SqlDependencySchemaOwner WITH DEFAULT_SCHEMA = SqlDependency;
--set the default schema of minimally privileged user to SqlDependency
ALTER USER SqlNotificationUser WITH DEFAULT_SCHEMA = SqlDependency;
--grant user control permissions on SqlDependency schema
GRANT CONTROL ON SCHEMA::SqlDependency TO SqlNotificationUser;
--grant cleanup user ability to see and cleanup services
GRANT ALTER ANY SERVICE TO SqlDependencySchemaOwner;
--grant user impersonate permissions on SqlDependency schema owner
GRANT IMPERSONATE ON USER::SqlDependencySchemaOwner TO SqlNotificationUser;
GO
--grant database permissions needed to create and use SqlDependency objects
GRANT CREATE PROCEDURE TO SqlNotificationUser;
GRANT CREATE QUEUE TO SqlNotificationUser;
GRANT CREATE SERVICE TO SqlNotificationUser;
GRANT REFERENCES ON
CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] TO SqlNotificationUser;
GRANT VIEW DEFINITION TO SqlNotificationUser;
GRANT SELECT to SqlNotificationUser;
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO SqlNotificationUser;
GRANT RECEIVE ON QueryNotificationErrorsQueue TO SqlNotificationUser;
GO