"dbo" schema permissions question.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

chris - 28 Jan 2006 21:28 GMT

I have some ASP.NET 2.0 code executing
SqlDependency.Start(connectionstring) to a SQL 2005 database and I am
getting the following error:

System.Data.SqlClient.SqlException: The specified schema name "dbo"
either does not exist or you do not have permission to use it.

As a general SQL 2005 Security question, can anyone please inform me as
to how i would give my user permissions to the "dbo" schema? At
present the user has "dbo" set up as it's default schema, but I am not
sure what that does. Also my code works fine if I give the user
sysadmin rights.

Thanks,
Chris

Reply to this Message

Bob Beauchemin - 28 Jan 2006 23:21 GMT

Hi Chris,

It would probably be better to have the user in SqlDependency.Start have a
different default schema than dbo (create schema foo authorization youruser;
alter user youruser with default_schema = foo). Otherwise you would have to
give it access to create objects in the dbo schema. Once you've done that,
the user needs CREATE SERVICE, CREATE QUEUE, and CREATE PROCEDURE. Because
SERVICE is at database scope (not schema scope) its queue or procedure
that's causing the error. Don't forget GRANT SUBSCRIBE QUERY NOTIFICATION to
the user executing the commands too.

Cheers,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb

>I have some ASP.NET 2.0 code executing
> SqlDependency.Start(connectionstring) to a SQL 2005 database and I am
[quoted text clipped - 11 lines]
> Thanks,
> Chris

Reply to this Message

chris - 30 Jan 2006 01:17 GMT

Bob,

First of all thanks for your article about this subject back when it
was still in beta.

Your solution worked! Very easy, thanks for your help.

Chris

Reply to this Message