SQLDependency SQL Server 2008 R2

Question

I have been trying to set up SQL Notifications on SQL Server 2008 R2 but I keep getting the 'Cannot find the specified user 'owner'' error as mentioned in the articles below.

I know several people have submitted answers to this and I've looked at all the various combinations such as

SQLDependency Caching not working

http://blogs.msdn.com/b/dataaccess/archive/2005/09/27/474447.aspx

http://keithelder.net/2009/01/20/sqldependency-and-sql-service-broker-permissions/

http://www.codeproject.com/Articles/12862/Minimum-Database-Permissions-Required-for-SqlDepen

https://social.msdn.microsoft.com/Forums/en-US/99321f54-1fef-4860-9fe9-5966a46fe582/once-for-all-right-permissions-for-sqldependency-please?forum=sqlservicebroker

https://dba.stackexchange.com/questions/47567/permissions-using-net-sqldependency

Some of these use a user account for the permissions and others use a role.

Here's the script that I am using:

CREATE LOGIN risk_test WITH PASSWORD = 'Password1', CHECK_POLICY = OFF;
GO

CREATE USER risk_test FOR LOGIN risk_test;
GO

CREATE ROLE [sql_dependency] AUTHORIZATION [dbo];
GO

CREATE SCHEMA [sql_dependency] AUTHORIZATION [sql_dependency]
GO

EXECUTE sp_addrolemember N'sql_dependency', N'risk_test';
GO

ALTER USER [risk_test] WITH DEFAULT_SCHEMA=[sql_dependency]
GO

--Database level permissions
GRANT SELECT TO [sql_dependency];
GRANT CREATE PROCEDURE TO [sql_dependency];
GRANT CREATE QUEUE TO [sql_dependency];
GRANT CREATE SERVICE to [sql_dependency];
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [sql_dependency];
GRANT VIEW DEFINITION TO [sql_dependency];
GRANT ALTER ON SCHEMA::sql_dependency TO [risk_test]
GO

--Service broker permissions
GRANT REFERENCES ON CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] TO [sql_dependency];
GRANT RECEIVE ON QueryNotificationErrorsQueue TO [sql_dependency];
GO

GRANT IMPERSONATE ON USER::dbo TO [risk_test];
GO

I ran a profiler and see the following sequence:

select is_broker_enabled from sys.databases where database_id=db_id()

CREATE PROCEDURE [SqlQueryNotificationStoredProcedure-778b1ff4-6d73-46d6-bee9-fc05272fe8d7] AS BEGIN BEGIN TRANSACTION; RECEIVE TOP(0) conversation_handle FROM [SqlQueryNotificationService-778b1ff4-6d73-46d6-bee9-fc05272fe8d7]; IF (SELECT COUNT(*) FROM [SqlQueryNotificationService-778b1ff4-6d73-46d6-bee9-fc05272fe8d7] WHERE message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/DialogTimer') > 0 BEGIN if ((SELECT COUNT(*) FROM sys.services WHERE name = 'SqlQueryNotificationService-778b1ff4-6d73-46d6-bee9-fc05272fe8d7') > 0)   DROP SERVICE [SqlQueryNotificationService-778b1ff4-6d73-46d6-bee9-fc05272fe8d7]; if (OBJECT_ID('SqlQueryNotificationService-778b1ff4-6d73-46d6-bee9-fc05272fe8d7', 'SQ') IS NOT NULL)   DROP QUEUE [SqlQueryNotificationService-778b1ff4-6d73-46d6-bee9-fc05272fe8d7]; DROP PROCEDURE [SqlQueryNotificationStoredProcedure-778b1ff4-6d73-46d6-bee9-fc05272fe8d7]; END COMMIT TRANSACTION; END

declare @p3 uniqueidentifier
set @p3=NULL
exec sp_executesql N'IF OBJECT_ID(''SqlQueryNotificationService-778b1ff4-6d73-46d6-bee9-fc05272fe8d7'', ''SQ'') IS NULL BEGIN CREATE QUEUE [SqlQueryNotificationService-778b1ff4-6d73-46d6-bee9-fc05272fe8d7] WITH ACTIVATION (PROCEDURE_NAME=[SqlQueryNotificationStoredProcedure-778b1ff4-6d73-46d6-bee9-fc05272fe8d7], MAX_QUEUE_READERS=1, EXECUTE AS OWNER); END; IF (SELECT COUNT(*) FROM sys.services WHERE NAME=''SqlQueryNotificationService-778b1ff4-6d73-46d6-bee9-fc05272fe8d7'') = 0 BEGIN CREATE SERVICE [SqlQueryNotificationService-778b1ff4-6d73-46d6-bee9-fc05272fe8d7] ON QUEUE [SqlQueryNotificationService-778b1ff4-6d73-46d6-bee9-fc05272fe8d7] ([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]); IF (SELECT COUNT(*) FROM sys.database_principals WHERE name=''sql_dependency_subscriber'' AND type=''R'') <> 0 BEGIN GRANT SEND ON SERVICE::[SqlQueryNotificationService-778b1ff4-6d73-46d6-bee9-fc05272fe8d7] TO sql_dependency_subscriber; END;  END; BEGIN DIALOG @dialog_handle FROM SERVICE [SqlQueryNotificationService-778b1ff4-6d73-46d6-bee9-fc05272fe8d7] TO SERVICE ''SqlQueryNotificationService-778b1ff4-6d73-46d6-bee9-fc05272fe8d7''',N'@dialog_handle uniqueidentifier output',@dialog_handle=@p3 output
select @p3

We do not want to add the grant control on dbo to our user as this opens a security hole. Does anyone know what is missing from my script to make this work?

Answer 1

http://www.codeproject.com/Articles/12862/Minimum-Database-Permissions-Required-for-SqlDepen

Has good advice:

"It is vital that we create a schema specifically for [risk_test] and that we make this user the owner of this schema. We also need to make sure that the default schema of this user is set to this new schema. If we wouldn't do this, then SqlDependency.Start would attempt to create some queues and stored procedures in the user's default schema which is dbo. This would fail since [risk_test] does not have sufficient rights to control the dbo-schema. Since we want to know the minimum rights [risk_test] needs to run SqlDependency.Start, we don't want to give him dbo priviliges. Creating a separate schema ensures that SqlDependency.Start can create the necessary objects inside this [risk_test] schema without compromising security."

So my advice - ensure that the connection string to the database uses the [risk_test] credentials.