Click here to Skip to main content
15,867,308 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 181.5K   996   70   30
This article explains how to give a user the minimum rights required for SqlDependency to work

Introduction

.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.
SQL
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]
GO

-- Ensuring that Service Broker is enabled 
ALTER DATABASE [SqlDependencyTest] SET ENABLE_BROKER
GO 

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

-- 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))
GO

/*
 * 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] 
WITH DEFAULT_SCHEMA = [startUser]
GO
CREATE USER [subscribeUser] FOR LOGIN [subscribeUser]
GO

/*
 * 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.
 */
CREATE SCHEMA [startUser] AUTHORIZATION [startUser]
GO

/*
 * 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]
GRANT REFERENCES on 
CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]
  to [sql_dependency_starter] 
GRANT VIEW DEFINITION TO [sql_dependency_starter] 

-- Permissions needed for [sql_dependency_subscriber] 
GRANT SELECT to [sql_dependency_subscriber] 
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [sql_dependency_subscriber] 
GRANT RECEIVE ON QueryNotificationErrorsQueue TO [sql_dependency_subscriber] 
GRANT REFERENCES on 
CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]
  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:

C#
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...
      SqlDependency.Start(mStarterConnectionString);

      // Registering for changes... 
      RegisterForChanges();

      // 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("notified.\n");
      Console.WriteLine("Press enter to quit this program.");
      Console.ReadLine();

      // Quitting...
      SqlDependency.Stop(mStarterConnectionString);
    }

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

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

Points of Interest

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

License

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 http://www.adamsoftware.net

Comments and Discussions

 
QuestionOnNotificationChange Pin
kiquenet.com18-Sep-22 6:52
professionalkiquenet.com18-Sep-22 6:52 
QuestionDon't forget to unsubscribe the event handler if you are intending on looking for additional change events Pin
justinhca31-Jan-19 12:36
justinhca31-Jan-19 12:36 
PraiseVote of 5 Pin
Gerardo Java19-Sep-17 22:10
Gerardo Java19-Sep-17 22:10 
NewsSQL Express 2016 Pin
martinrj3027-Jul-17 19:59
martinrj3027-Jul-17 19:59 
Questionwinform application Pin
sirboy30-Apr-17 11:38
sirboy30-Apr-17 11:38 
QuestionThe clearest SqlDependency sample I found Pin
Member 96699281-Oct-14 21:22
Member 96699281-Oct-14 21:22 
GeneralMy vote of 5 Pin
Joezer BH27-Nov-12 23:41
professionalJoezer BH27-Nov-12 23:41 
QuestionWhy is this not on MSDN? Pin
L8Again27-Oct-11 23:46
L8Again27-Oct-11 23:46 
I literally wasted 2 days trying to get SQLCacheDependency to work on an ASP.NET application. Even though this article doesn't speak to that directly, it helped me more than any article anywhere. Most complete and succinct example I have seen on the subject so far. Keep up the good work.
GeneralMy vote of 5 Pin
zeekiorage19-Oct-10 12:38
zeekiorage19-Oct-10 12:38 
GeneralTrouble when changed to Stored Procedure Pin
Rick Weyrauch29-Jan-10 3:54
Rick Weyrauch29-Jan-10 3:54 
GeneralRe: Trouble when changed to Stored Procedure Pin
Rick Weyrauch29-Jan-10 4:08
Rick Weyrauch29-Jan-10 4:08 
Questionsqldependency in win forms Pin
Naveen Kumar Sunnam29-Dec-08 1:43
Naveen Kumar Sunnam29-Dec-08 1:43 
AnswerRe: sqldependency in win forms Pin
Michael Vanhoutte30-Dec-08 4:04
Michael Vanhoutte30-Dec-08 4:04 
QuestionGetting an error while starting SqlDependency Pin
Samnang Chhun6-May-08 16:25
Samnang Chhun6-May-08 16:25 
AnswerRe: Getting an error while starting SqlDependency Pin
smt522-Jan-14 6:21
smt522-Jan-14 6:21 
Questionwhat if the database user already exists? Pin
lax4u7-Apr-08 11:52
lax4u7-Apr-08 11:52 
AnswerRe: what if the database user already exists? Pin
Michael Vanhoutte15-Apr-08 8:43
Michael Vanhoutte15-Apr-08 8:43 
GeneralIn my case, both users set their default schema to dbo, but still get no permission logged, Pin
wyx200031-Mar-08 13:19
wyx200031-Mar-08 13:19 
Generalsql_dependency_subscriber is this a special role Pin
Milind Y27-Mar-08 20:16
Milind Y27-Mar-08 20:16 
GeneralRe: sql_dependency_subscriber is this a special role Pin
Michael Vanhoutte3-Apr-08 20:51
Michael Vanhoutte3-Apr-08 20:51 
GeneralHelped a lot, and there's a bug in SQL Server with it Pin
Martin Sp.24-Jan-08 7:02
Martin Sp.24-Jan-08 7:02 
GeneralRe: Helped a lot, and there's a bug in SQL Server with it Pin
Michael Vanhoutte25-Jan-08 20:21
Michael Vanhoutte25-Jan-08 20:21 
GeneralRe: Helped a lot, and there's a bug in SQL Server with it Pin
Martin Sp.8-Nov-11 9:07
Martin Sp.8-Nov-11 9:07 
GeneralGreat! Pin
Mike Eby15-Jan-08 10:53
Mike Eby15-Jan-08 10:53 
QuestionIs GRANT VIEW DEFINITION necessary? Pin
Mikael Jirhage2-Oct-06 2:01
Mikael Jirhage2-Oct-06 2:01 

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.