SqlTableDependency Permission Issues

7
votes

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?

3
c#sql-serversql-server-2008permissions
is this a server you set up? - DForck42
Yes, I set this up myself. - RickLeinecker
when you installed sql server, did you set up an admin account? - DForck42

3 Answers

2
votes

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.

2
votes

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.

1
votes

My problem too.

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