{"id":880,"date":"2006-01-30T18:35:00","date_gmt":"2006-01-30T18:35:00","guid":{"rendered":"\/blogs\/bobb\/post\/About-SCHEMAs-and-setting-up-Query-Notifications.aspx"},"modified":"2013-01-04T00:00:17","modified_gmt":"2013-01-04T08:00:17","slug":"about-schemas-and-setting-up-query-notifications","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/bobb\/about-schemas-and-setting-up-query-notifications\/","title":{"rendered":"About SCHEMAs and setting up Query Notifications"},"content":{"rendered":"

\nI'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 <\/a>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.\n<\/p>\n

\nThe instructions don't show creating 'startUser' (the principal that creates procedures, queues, and services), so folks create it, using the new DDL, like this:\n<\/p>\n

\nCREATE LOGIN startUser WITH PASSWORD = 'SomeStrongPW1'
\nCREATE USER startUser FOR LOGIN startUser\n<\/p>\n

\nProblem 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.\n<\/p>\n

\nCREATE SCHEMA startUserSchema AUTHORIZATION startUser
\nALTER USER startUser WITH DEFAULT_SCHEMA = startUserSchema\n<\/p>\n

\nA 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"?\n<\/p>\n

\nA less attractive (actually unattractive) alternative is to give startUser CREATE (actually ALTER) privilege on the DBO schema.\n<\/p>\n

\nGRANT ALTER ON SCHEMA::dbo to startUser\n<\/p>\n

\nDON'T do this, you've just given startUser much more privilege than it really needs.\n<\/p>\n

\nThree more comments:
\n1. If you used sp_adduser instead of CREATE USER, you "got lucky". For backward compatibility sp_adduser actually does:\n<\/p>\n

\nCREATE USER startUser WITH DEFAULT_SCHEMA = startUser
\nGO
\n— create schema must be first statement in the batch
\nCREATE SCHEMA startUser AUTHORIZATION startUser\n<\/p>\n

\nWhen\/if that backward compatibility mode is removed, your luck runs out.\n<\/p>\n

\n2. 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.\n<\/p>\n

\n3. 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.<\/p>\n","protected":false},"excerpt":{"rendered":"

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 […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[22,34],"tags":[],"class_list":["post-880","post","type-post","status-publish","format-standard","hentry","category-query-notifications","category-sql-server-schemas"],"yoast_head":"\nAbout SCHEMAs and setting up Query Notifications - Bob Beauchemin<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/about-schemas-and-setting-up-query-notifications\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"About SCHEMAs and setting up Query Notifications - Bob Beauchemin\" \/>\n<meta property=\"og:description\" content=\"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 […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/bobb\/about-schemas-and-setting-up-query-notifications\/\" \/>\n<meta property=\"og:site_name\" content=\"Bob Beauchemin\" \/>\n<meta property=\"article:published_time\" content=\"2006-01-30T18:35:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-01-04T08:00:17+00:00\" \/>\n<meta name=\"author\" content=\"Bob Beauchemin\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Bob Beauchemin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/about-schemas-and-setting-up-query-notifications\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/about-schemas-and-setting-up-query-notifications\/\",\"name\":\"About SCHEMAs and setting up Query Notifications - Bob Beauchemin\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\"},\"datePublished\":\"2006-01-30T18:35:00+00:00\",\"dateModified\":\"2013-01-04T08:00:17+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/about-schemas-and-setting-up-query-notifications\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/bobb\/about-schemas-and-setting-up-query-notifications\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/about-schemas-and-setting-up-query-notifications\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Query Notifications\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/query-notifications\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"About SCHEMAs and setting up Query Notifications\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/\",\"name\":\"Bob Beauchemin\",\"description\":\"SQL Server Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e\",\"name\":\"Bob Beauchemin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g\",\"caption\":\"Bob Beauchemin\"},\"sameAs\":[\"http:\/www.sqlskills.com\/blogs\/bobb\/\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/bobb\/author\/bobb\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"About SCHEMAs and setting up Query Notifications - Bob Beauchemin","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/bobb\/about-schemas-and-setting-up-query-notifications\/","og_locale":"en_US","og_type":"article","og_title":"About SCHEMAs and setting up Query Notifications - Bob Beauchemin","og_description":"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 […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/about-schemas-and-setting-up-query-notifications\/","og_site_name":"Bob Beauchemin","article_published_time":"2006-01-30T18:35:00+00:00","article_modified_time":"2013-01-04T08:00:17+00:00","author":"Bob Beauchemin","twitter_misc":{"Written by":"Bob Beauchemin","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/about-schemas-and-setting-up-query-notifications\/","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/about-schemas-and-setting-up-query-notifications\/","name":"About SCHEMAs and setting up Query Notifications - Bob Beauchemin","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website"},"datePublished":"2006-01-30T18:35:00+00:00","dateModified":"2013-01-04T08:00:17+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/about-schemas-and-setting-up-query-notifications\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/bobb\/about-schemas-and-setting-up-query-notifications\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/about-schemas-and-setting-up-query-notifications\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/"},{"@type":"ListItem","position":2,"name":"Query Notifications","item":"https:\/\/www.sqlskills.com\/blogs\/bobb\/category\/query-notifications\/"},{"@type":"ListItem","position":3,"name":"About SCHEMAs and setting up Query Notifications"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/","name":"Bob Beauchemin","description":"SQL Server Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/bobb\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/62bfa986c5b5d28fcffd8b4fc409c73e","name":"Bob Beauchemin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/bobb\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/6f80e6cc667410857fa6a21931dc528b8092f4d112bf7a8ff7c267674d44ee37?s=96&d=mm&r=g","caption":"Bob Beauchemin"},"sameAs":["http:\/www.sqlskills.com\/blogs\/bobb\/"],"url":"https:\/\/www.sqlskills.com\/blogs\/bobb\/author\/bobb\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/880","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/comments?post=880"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/posts\/880\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/media?parent=880"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/categories?post=880"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/bobb\/wp-json\/wp\/v2\/tags?post=880"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}