Emil's Wicked Cool Blog

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]