Click here to Skip to main content
15,794,202 members
Articles / Database Development / SQL Server

Minimum Database Permissions Required for SqlDependency

Rate me:
Please Sign up or sign in to vote.
4.71/5 (37 votes)
22 Mar 2006CPOL2 min read 179.4K   994   70   30
This article explains how to give a user the minimum rights required for SqlDependency to work


.NET 2.0 has a cool new feature called SqlDependency which provides a mechanism to notify an application when a cache is invalidated. We're not going to go explain the use of this technology in this article because there are plenty of good resources already available like this one. This article is going to show you what minimum rights a SQL Server 2005 user should have for SqlDependency to work. Sushil Cordia has a blog on MSDN that describes most of these rights, but I have noticed that this blog doesn't list all rights required. Hence, this article.

Using the Code

There are two pieces of code that accompany this article. The first is a SQL Script that sets the necessary permissions; the other is a C# console application that uses SqlDependency to watch for changes in a specific table.

My original idea was to create two roles in SQL Server that have all the permissions required to run SQLDependency; one role that has all the permissions to start the SqlDependency listener and the other that has the permissions required to subscribe for changes. However, I have noticed that this is not possible. The problem with having only two roles is that the users that are members of these roles will belong to the dbo-schema. This causes problems when trying to run SqlDependency.Start because this method attempts to create a queue in the schema of the user and it doesn't have sufficient rights to do so. Therefore, the solution mentioned in this article creates these two roles containing the required permissions, but you should also make sure that the user that starts SqlDependency has its own schema of which it is the owner.

The following is the SQL Script that does the following:

  • Creates a test database called SqlDependencyTest
  • Creates a user called startUser which is a user that will have sufficient rights to call SqlDependency.Start
  • Creates a user called subscribeUser which is a user that will have sufficient rights to subscribe for change notifications
  • Creates a role called sql_dependency_starter that has some permissions set that gives all the members of this role sufficient rights to run SqlDependency.Start
  • Creates a role called sql_dependency_subscriber that has some permissions set that gives all the members of this role sufficient rights to subscribe for notifications.
USE master

-- Cleaning up before we start
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'SqlDependencyTest')
DROP DATABASE [SqlDependencyTest]
IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'startUser')
DROP LOGIN [startUser]
IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'subscribeUser')
DROP LOGIN [subscribeUser]

-- Creating a database
CREATE DATABASE [SqlDependencyTest]

-- Ensuring that Service Broker is enabled 

-- Creating users
CREATE LOGIN [startUser] WITH PASSWORD=N'startUser', 
CREATE LOGIN [subscribeUser] WITH PASSWORD=N'subscribeUser', 
            DEFAULT_DATABASE=[SqlDependencyTest], CHECK_EXPIRATION=OFF, 

-- Switching to our database
use [SqlDependencyTest]

-- Creating a table. All changes made to the contents of this table will be
-- monitored.
CREATE TABLE Users (ID int, Name nvarchar(50))

 * Creating the users in this database
 * We're going to create two users. One called startUser. This is the user 
 * that is going to have sufficient rights to run SqlDependency.Start.
 * The other user is called subscribeUser, and this is the user that is 
 * going to actually register for changes on the Users-table created earlier.
 * Technically, you're not obligated to make two different users naturally, 
 * but I did here anyway to make sure that I know the minimal rights required
 * for both operations
 * Pay attention to the fact that the startUser-user has a default schema set.
 * This is critical for SqlDependency.Start to work. Below is explained why.
CREATE USER [startUser] FOR LOGIN [startUser] 
CREATE USER [subscribeUser] FOR LOGIN [subscribeUser]

 * Creating the schema
 * It is vital that we create a schema specifically for startUser and that we
 * make this user the owner of this schema. We also need to make sure that 
 * the default schema of this user is set to this new schema (we have done 
 * this earlier)
 * If we wouldn't do this, then SqlDependency.Start would attempt to create 
 * some queues and stored procedures in the user's default schema which is
 * dbo. This would fail since startUser does not have sufficient rights to 
 * control the dbo-schema. Since we want to know the minimum rights startUser
 * needs to run SqlDependency.Start, we don't want to give him dbo priviliges.
 * Creating a separate schema ensures that SqlDependency.Start can create the
 * necessary objects inside this startUser schema without compromising 
 * security.

 * Creating two new roles. We're not going to set the necessary permissions 
 * on the user-accounts, but we're going to set them on these two new roles.
 * At the end of this script, we're simply going to make our two users 
 * members of these roles.
EXEC sp_addrole 'sql_dependency_subscriber' 
EXEC sp_addrole 'sql_dependency_starter' 

-- Permissions needed for [sql_dependency_starter]
GRANT CREATE PROCEDURE to [sql_dependency_starter] 
GRANT CREATE QUEUE to [sql_dependency_starter]
GRANT CREATE SERVICE to [sql_dependency_starter]
  to [sql_dependency_starter] 
GRANT VIEW DEFINITION TO [sql_dependency_starter] 

-- Permissions needed for [sql_dependency_subscriber] 
GRANT SELECT to [sql_dependency_subscriber] 
GRANT RECEIVE ON QueryNotificationErrorsQueue TO [sql_dependency_subscriber] 
  to [sql_dependency_subscriber] 

-- Making sure that my users are member of the correct role.
EXEC sp_addrolemember 'sql_dependency_starter', 'startUser'
EXEC sp_addrolemember 'sql_dependency_subscriber', 'subscribeUser'

All we need now is a test application that uses these two users and ensures that SqlDependency works:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;

namespace SqlDependencyTest
  class Program
    private static string mStarterConnectionString = 
@"Data Source=(local);Database=SqlDependencyTest;Persist Security Info=false;
  Integrated Security=false;User Id=startUser;Password=startUser";
    private static string mSubscriberConnectionString = 
@"Data Source=(local);Database=SqlDependencyTest;Persist Security Info=false;
Integrated Security=false;User Id=subscribeUser;Password=subscribeUser";

    static void Main(string[] args)
      // Starting the listener infrastructure...

      // Registering for changes... 

      // Waiting...
      Console.WriteLine("At this point, you should start the Sql Server ");
      Console.WriteLine("Management Studio and make ");
      Console.WriteLine("some changes to the Users table that you'll find");
      Console.WriteLine(" in the SqlDependencyTest ");
      Console.WriteLine("database. Every time a change happens in this ");
      Console.WriteLine("table, this program should be ");
      Console.WriteLine("Press enter to quit this program.");

      // Quitting...

    public static void RegisterForChanges()
      // Connecting to the database using our subscriber connection string 
      // and waiting for changes...
      SqlConnection oConnection 
                          = new SqlConnection(mSubscriberConnectionString);
        SqlCommand oCommand = new SqlCommand(
          "SELECT ID, Name FROM dbo.Users",
        SqlDependency oDependency = new SqlDependency(oCommand);
        oDependency.OnChange += new OnChangeEventHandler(OnNotificationChange);
        SqlDataReader objReader = oCommand.ExecuteReader();
          while (objReader.Read())
            // Doing something here...

    public static void OnNotificationChange(object caller, 
                                            SqlNotificationEventArgs e)
      Console.WriteLine(e.Info.ToString() + ": " + e.Type.ToString());

Points of Interest

Many thanks to my Microsoft buddy, Nicole Haugen, for helping me solve some of the problems encountered.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By
Team Leader
Belgium Belgium
I am a developer spending most of my time in C#, .NET 2.0 and Sql Server 2005. I am working for a Belgium company called Adam Software developing Asset Management Software. More information about my company and our software can be found at

Comments and Discussions

GeneralRe: Helped a lot, and there's a bug in SQL Server with it Pin
Martin Sp.8-Nov-11 10:07
Martin Sp.8-Nov-11 10:07 
GeneralGreat! Pin
Mike Eby15-Jan-08 11:53
Mike Eby15-Jan-08 11:53 
QuestionIs GRANT VIEW DEFINITION necessary? Pin
Mikael Jirhage2-Oct-06 3:01
Mikael Jirhage2-Oct-06 3:01 
AnswerRe: Is GRANT VIEW DEFINITION necessary? Pin
Michael Vanhoutte2-Oct-06 22:37
Michael Vanhoutte2-Oct-06 22:37 
GeneralExcelent!!! Pin
marcondesf25-Sep-06 4:39
marcondesf25-Sep-06 4:39 
GeneralYep Pin
Marc Leger27-Jul-06 12:28
Marc Leger27-Jul-06 12:28 
GeneralRe: Yep Pin
Member 259468713-Jun-08 5:15
Member 259468713-Jun-08 5:15 
Generalnice Pin
AnasHashki22-Mar-06 3:42
AnasHashki22-Mar-06 3:42 
keep the good work Wink | ;) Smile | :)

Software Developer
LIFE'S SHORT. If you don't look around once in a while you might miss it

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.