I've answered a few questions lately on setting up SqlDependency or ASP.NET SQL Server dependency with SQL Server 2005. Folks have gone by the instructions on the DataWorks Weblog posting and still receive the error "either schema dbo does not exist or you do not have permission to access it". This is caused by the separation or users and schemas in SQL Server 2005.
The instructions don't show creating 'startUser' (the principal that creates procedures, queues, and services), so folks create it, using the new DDL, like this:
CREATE LOGIN startUser WITH PASSWORD = 'SomeStrongPW1'
CREATE USER startUser FOR LOGIN startUser
Problem is, CREATE USER doesn't assign a default database schema (its not supposed to) and when startUser attempts to create database objects, it creates them in the "default default_schema" which is dbo. The quick fix is to create a schema for (owned by) the user and make that schema its default schema.
CREATE SCHEMA startUserSchema AUTHORIZATION startUser
ALTER USER startUser WITH DEFAULT_SCHEMA = startUserSchema
A better alternative might be to create a database role for this function and create the default schema owned by the role. Then add startUser to the role. You still have to alter the user's default_schema in this case, because database roles cannot have default schemas. Roles cannot have default_schemas themselves because if one user was a member of 3 different roles and each role had a different default_schemas which one would "win"?
A less attractive (actually unattractive) alternative is to give startUser CREATE (actually ALTER) privilege on the DBO schema.
GRANT ALTER ON SCHEMA::dbo to startUser
DON'T do this, you've just given startUser much more privilege than it really needs.
Three more comments:
1. If you used sp_adduser instead of CREATE USER, you "got lucky". For backward compatibility sp_adduser actually does:
CREATE USER startUser WITH DEFAULT_SCHEMA = startUser
GO
— create schema must be first statement in the batch
CREATE SCHEMA startUser AUTHORIZATION startUser
When/if that backward compatibility mode is removed, your luck runs out.
2. With the new separation of users and schemas granting CREATE TABLE permission doesn't give the user enough to create a TABLE, nowadays. The user also needs a *container* to create tables (or other database objects) in. The user needs a database schema. Resist the temptation to make a schema for the user; rather make a schema for a role the user is a member of. The fact that schemas can be owned by a role is one of their best features.
3. Technically, Service Broker SERVICEs (as well as MESSAGE_TYPEs and CONTACTs) don't live at schema scope. They live at database scope, so you don't need a schema for them. CREATE privilege is enough. But QUEUEs (and most database objects) do live at schema scope. QUEUEs are just tables with special semantics, after all.