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?
Best Answer
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.