Database permissions for SQLDependency

Thursday, March 20, 2008

While I like the idea of SQL 2005 Query notifications, the setup restrictions and instructions are fairly opaque. Blah! I did manage to get it working after noting all the restrictions on the query in this MSDN article, but then I made the mistake of removing dbo permissions from the user, and was thrown into the mix again for another hour of churning.

This blog post was pretty useful, but didn't go quite all the way. Later, I found a post describing more details after some searching, and came up with this set of grant statements to make it work:

  • GRANT ALTER ON SCHEMA :: [schemaname] TO [Role]
  • GRANT CREATE PROCEDURE TO [Role]
  • GRANT CREATE QUEUE TO [Role]
  • GRANT CREATE SERVICE TO [Role]
  • GRANT REFERENCES on CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to [Role]
  • GRANT VIEW DEFINITION TO [Role]
  • GRANT SELECT to [Role]
  • GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [Role]
  • GRANT RECEIVE ON QueryNotificationErrorsQueue TO [Role]
  • GRANT IMPERSONATE ON USER::DBO TO [Role]


2 comments:

Anonymous said...

This saved me a lot of time, but you missed one thing. You need to add the role sql_dependency_subscriber then add the user to that role.

Steve Smith said...

Great and Useful Article.

Java Online Training

Java Course Online

Java EE course

Java Course in Chennai

Java Training in Chennai

Java Training Institutes in Chennai

Java Interview Questions

Java Interview Questions

Emil's Wicked Cool Blog