Click here to Skip to main content
Click here to Skip to main content

Minimum Database Permissions Required for SqlDependency

, 22 Mar 2006 CPOL
Rate this:
Please Sign up or sign in to vote.
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.
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:

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)

Share

About the Author

Michael Vanhoutte
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

 
QuestionThe clearest SqlDependency sample I found PinmemberMember 96699281-Oct-14 22:22 
GeneralMy vote of 5 PinmemberEdo Tzumer28-Nov-12 0:41 
QuestionWhy is this not on MSDN? PinmemberL8Again28-Oct-11 0:46 
GeneralMy vote of 5 Pinmemberzeekiorage19-Oct-10 13:38 
GeneralTrouble when changed to Stored Procedure PinmemberRick Weyrauch29-Jan-10 4:54 
GeneralRe: Trouble when changed to Stored Procedure PinmemberRick Weyrauch29-Jan-10 5:08 
Questionsqldependency in win forms Pinmembersnaveenkumareee29-Dec-08 2:43 
AnswerRe: sqldependency in win forms PinmemberMichael Vanhoutte30-Dec-08 5:04 
QuestionGetting an error while starting SqlDependency PinmemberSamnang Chhun6-May-08 17:25 
AnswerRe: Getting an error while starting SqlDependency [modified] Pinmembersmt522-Jan-14 7:21 
Questionwhat if the database user already exists? Pinmemberlax4u7-Apr-08 12:52 
AnswerRe: what if the database user already exists? PinmemberMichael Vanhoutte15-Apr-08 9:43 
GeneralIn my case, both users set their default schema to dbo, but still get no permission logged, Pinmemberwyx200031-Mar-08 14:19 
Generalsql_dependency_subscriber is this a special role PinmemberMilind Y27-Mar-08 21:16 
GeneralRe: sql_dependency_subscriber is this a special role PinmemberMichael Vanhoutte3-Apr-08 21:51 
GeneralHelped a lot, and there's a bug in SQL Server with it PinmemberMartin Sp.24-Jan-08 8:02 
GeneralRe: Helped a lot, and there's a bug in SQL Server with it PinmemberMichael Vanhoutte25-Jan-08 21:21 
GeneralRe: Helped a lot, and there's a bug in SQL Server with it PinmemberMartin Sp.8-Nov-11 10:07 
GeneralGreat! PinmemberMike Eby15-Jan-08 11:53 
QuestionIs GRANT VIEW DEFINITION necessary? PinmemberMikael Jirhage2-Oct-06 3:01 
AnswerRe: Is GRANT VIEW DEFINITION necessary? PinmemberMichael Vanhoutte2-Oct-06 22:37 
GeneralExcelent!!! Pinmembermarcondesf25-Sep-06 4:39 
GeneralYep PinmemberMarc Leger27-Jul-06 12:28 
GeneralRe: Yep PinmemberMember 259468713-Jun-08 5:15 
Generalnice PinmemberThe_Myth22-Mar-06 3:42 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.141223.1 | Last Updated 22 Mar 2006
Article Copyright 2006 by Michael Vanhoutte
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid