Minimum Database Permissions Required for SqlDependency

• Download source - 3.84 KB

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.