SqlDependency not firing

Grrrr...after hours of trying various pieces of C# code and tinkering with SQL Server 2005 settings it turns out that the database owner was the problem.

class Program
{
static string connStr = "Server=DARKSTAR; Initial Catalog=MyDatabase; Integrated Security=false; User Id=sa; Password=XXXXXXXXXX";

static SqlDependency dep;

static void Main(string[] args)
{
SqlDependency.Start(connStr);
TestSqlNotificiation();
SqlDependency.Stop(connStr);
}

static void TestSqlNotificiation()
{
try
{
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();

SqlCommand cmd = conn.CreateCommand();

cmd.CommandText = "Select RollNo, [Name] from dbo.Students";

dep = new SqlDependency(cmd);

dep.OnChange += new OnChangeEventHandler(OnDataChange);

SqlDataReader dr = cmd.ExecuteReader();
{
while (dr.Read())
{
Console.WriteLine("Name = " + dr[1].ToString());
}
}

dr.Close();

Console.WriteLine("Waiting for any data changes...\nPress to end program.");
Console.ReadLine();
}
}
finally
{
//SqlDependency.Stop(connStr);
}
}

static void OnDataChange(object sender, SqlNotificationEventArgs e)
{
Console.WriteLine(e.Info.ToString());
Console.WriteLine(e.Source.ToString());
}
}
}

This is the program which should fire the OnDataChange function when rows change in the Students table. However, the event was simply not firing.

Finally I chanced upon this article which made everything work right. It turned out that I was having problems with the database owner. I just changed it to 'sa' for the time-being:

ALTER AUTHORIZATION ON DATABASE::MyDatabase TO sa;