"dbo" schema permissions question.

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,
ChrisHi 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
"chris" <chris@.cubed-c.com> wrote in message
news:1138483703.893069.160850@.g43g2000cwa.googlegroups.com...
>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
>|||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

"Changed Context to ..." message when editing permissions w/ VBA

I have a client using a small SQL Express database with an Access front end.

I've created a form in the front end that allows a supervisor the ability to perform two very basic security operations. 1) Adding a new user, and 2) granting/removing write privileges. The code behind the form uses VBA to build some T-SQL strings and then executes them via an ADO connection.

The strings are as follows:

1) Add new login to server:
"CREATE LOGIN [NETWORK\UserName] FROM WINDOWS WITH DEFAULT_DATABASE = MyDatabase"

2) Add user to database:
"USE MyDatabase CREATE USER [NETWORK\UserName]"

3) Grant Write Permissions:
" USE MyDatabase EXEC sp_addrolemember 'db_datawriter', 'NETWORK\UserName' "

4) Remove Write Permissions:
" USE MyDatabase EXEC sp_droprolemember 'db_datawriter', 'NETWORK\UserName' "

I have full privileges over the server & database, and everything runs perfectly.

However, when the supervisor - whose login includes security admin privileges - tries this, he gets a message that says "Changed Context to MyDatabase, Error# -2147217900, 80040e14" and the code fails.

If I grant him "db_owner" status, the message does not come up and everything runs perfectly.

I'm not a security guru or anything, but I suspect that it's not the greatest idea to hand out db_owner privileges.

I ran a search, and the best I could come up with were a couple of KB articles:
http://support.microsoft.com/default.aspx?scid=KB;EN-US;197459
http://support.microsoft.com/default.aspx?scid=KB;EN-US;143339

I'm not even sure these articles are talking about what's happening..

Any ideas?When is the last time your boss ran an office update? Or more specifically, which MDAC is he using?|||When is the last time your boss ran an office update? Or more specifically, which MDAC is he using?
I'm not at the client's location right now, so I can't give a firm answer.. but I had someone there gather the following info..

Access is version 11.6566.8132 SP2

They're running XP Pro version 2002 SP2.

Per this page: http://support.microsoft.com/kb/231943/en-us they should be on at least MDAC 2.8 SP1. However based on the release dates shown on that page, I wouldn't be suprised if they were running 2.8 SP2.

Does that help?|||Ok, went by their office yesterday. MDAC is version 2.8.1117.0.

After a little more time spent, it appears as if this is the 'informational' message passed back to ODBC. It appears that I need to figure out how to trap for it in VB. The KB articles I posted got me started, but I need some more work..

I also seem to still have a SQL issue.. In the little bit of troubleshooting I had time for, it also appears that I have a 2nd error coming in behind the "changed context.." message. If the user is not a dbowner, it says that he doesn't have permissions to run these operations.

Is this correct? Is dbowner status required to change permissions of other users?