SqlTableDependency Permission Issues

Question

I am trying to use the SqlTableDependency class in my C# .Net application and I am unable to grant myself the required database permissions needed. Specifically, I need permissions:

• ALTER
• CONTROL
• CREATE CONTRACT
• CREATE MESSAGE TYPE
• CREATE PROCEDURE
• CREATE QUEUE
• CREATE SERVICE
• EXECUTE
• SELECT
• SUBSCRIBE QUERY NOTIFICATIONS
• VIEW DATABASE STATE
• VIEW DEFINITION
• CONNECT

The error message states:

"An unhandled exception of type 'TableDependency.SqlClient.Exceptions.UserWithNoPermissionException' occurred in TableDependency.SqlClient.dll. Additional information: User with no CREATE MESSAGE TYPE permission"

I have tried granting myself the permission using this query:

GRANT CREATE MESSAGE TYPE TO dbo

but I get this error:

"Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself."

I have confirmed that dbo is the owner of my database.

I am running Sql Server 2008 R2.

How can I grant myself permissions to my server?

Answer 1

Whatever credentials you used in your connection string needs to be db_owner of database. I made that user as db_owner and it worked for me.

Answer 2

Make sure to do this:

Enable broker

ALTER DATABASE Your_db_name SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;
Go

Add to db_owner

ALTER ROLE db_owner ADD MEMBER Your_user_db_name
GO

Set authorization

ALTER AUTHORIZATION ON DATABASE::Your_db_name TO Your_user_db_name;

I hope this help.

Answer 3

My problem too.

When downgrade SqlTableDependency verson from 4.6.7.8 to 4.6.7, my code is running well :)