Skip to main content

Posts

Showing posts from October, 2023

SqlDependency - getting the permissions working...

 After much banging my head against the internet here is a canonical example of how to configure SQL Server permissions, roles to get SqlDependency working in a .Net app. This is for sceneario where the identity running the .Net code is running as a configured user account with the db_datareader & db_datawriter. -- Create Schema User CREATE USER sql_dependency_schema_owner WITHOUT LOGIN; GO -- Create Schema for SqlDependency objects CREATE SCHEMA sql_dependency AUTHORIZATION sql_dependency_schema_owner; GO -- Create Role for users of [sql_dependency] CREATE ROLE sql_dependency_user; GO -- Grant role permissions GRANT CONTROL ON SCHEMA::sql_dependency TO sql_dependency_user; GRANT IMPERSONATE ON USER::sql_dependency_schema_owner TO sql_dependency_user; GRANT CREATE PROCEDURE TO sql_dependency_user; GRANT CREATE QUEUE TO sql_dependency_user; GRANT CREATE SERVICE TO sql_dependency_user; GRANT REFERENCES ON CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotificatio