I have a web service that uses ASP.NET cache and SqlDependency to invalidate the cache. The command used for SqlDependency relies on a stored procedure.
The problem is the web service database user is minimally privileged with only stored procedure execute permissions. SqlException "CREATE PROCEDURE permission denied" is raised on subscription because the user doesn't have CREATE PROCEDURE rights.
How can I solve giving the user minimum permissions?
SqlDependency creates and drops database objects dynamically to support query notifications. To allow a minimally permissioned user to execute these statements, one can create a separate schema for the SqlDependency objects and specify that as the read-only user's default schema. The user will of course need execute permissions on your stored procedure and also schema-qualify object names (a best practice), which are in a different schema (e.g. dbo).
Below is an annotated example script that creates the schema and grants permissions to an existing minimally privileged user (named SqlNotificationUser here).
--create user for schema ownership
CREATE USER SqlDependencySchemaOwner WITHOUT LOGIN;
GO
--needed to allow activated cleanup proc to drop SqlDependency service owned by minimally-privileged user
GRANT ALTER ANY SERVICE TO SqlDependencySchemaOwner;
GO
--create schema for SqlDependency objects CREATE SCHEMA SqlDependency
AUTHORIZATION SqlDependencySchemaOwner;
GO
--set default schema of SqlDependency schema owner (internal SqlDependecy procs do not schema-qualify object names)
ALTER USER SqlDependencySchemaOwner WITH DEFAULT_SCHEMA = SqlDependency;
--create minimally privileged login
CREATE LOGIN SqlNotificationUser WITH PASSWORD='SqlUserSt0rongP@assord';
--create minimally privileged user with default schema SqlDependency
CREATE USER SqlNotificationUser WITH DEFAULT_SCHEMA = SqlDependency;
--grant user control permissions on SqlDependency schema
GRANT CONTROL ON SCHEMA::SqlDependency TO SqlNotificationUser;
--grant user impersonate permissions on SqlDependency schema owner
GRANT IMPERSONATE ON USER::SqlDependencySchemaOwner TO SqlNotificationUser;
GO
--grant database permissions needed to create and use SqlDependency objects
GRANT CREATE PROCEDURE TO SqlNotificationUser;
GRANT CREATE QUEUE TO SqlNotificationUser;
GRANT CREATE SERVICE TO SqlNotificationUser;
GRANT REFERENCES ON CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] TO SqlNotificationUser;
GRANT VIEW DEFINITION TO SqlNotificationUser;
GRANT SELECT to SqlNotificationUser;
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO SqlNotificationUser;
GRANT RECEIVE ON QueryNotificationErrorsQueue TO SqlNotificationUser;
GO