Here's another blog posting to answer a question from over a month ago. With separation of users and schemas, its known that the CREATE USER DDL statement without a DEFAULT_SCHEMA parameter assigns a DEFAULT_SCHEMA of dbo. Which the new user usually has no access to. And that sp_adduser, for backward compatibility, will CREATE a SCHEMA […]
Bob Beauchemin
SQL Server Blog
Does sp_dropuser “do the right thing” with schemas?
Posted on: December 30, 2006 12:56 am
Another behavior that follows schemas, query plan reuse
Posted on: October 28, 2006 5:55 pm
When I discuss separation of users and schemas in SQL Server 2005, I usually think of ownership chains (that follow the object owner which is always the schema owner unless you change it) or object resolution (which now goes: special name, then default_schema/procedure schema, then DBO). Another item that now follows schemas rather than users […]
About SCHEMAs and setting up Query Notifications
Posted on: January 30, 2006 6:35 pm
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 […]
Dude, where’s my table?
Posted on: August 13, 2005 11:05 pm
In answering a question about schemas, users, and objects (search on "schemas" to see the blog series I, II, III), I realized I never posted the portion about object resolution. Here goes. When SQL Server resolves a one-part object name, the object resolution is slightly different if you're inside a stored procedure. If batch or […]
Schemas, Users, and Objects – III
Posted on: December 3, 2004 6:18 pm
Now, back to our regularly scheduled technical content. About schemas, users, and owners. Although Ed originally created the table, since Fred is the schema owner, Fred owns to table. Ed can get ownership of the table in either of two ways. 1. Someone with authority can alter the table's owner 2. Someone with authority can […]
Schemas, Users, and Objects – II
Posted on: December 2, 2004 7:00 pm
The code for the answer is below. Greg Low is, of course, correct. Fred owns the table, but he owns it by virtue of being the schema owner. Now here's part 2. Do the following: 1. Alter the authorization on Ed's table so that it is owned by Ed. (Interesting aside, can Ed do […]
Schemas, Users, and Objects – I
Posted on: December 2, 2004 7:43 am
UI can't stay away from the separation and users and schemas feature. I want to make sure I have it cold, and following up on information I got from Girish Chandler's talk at Win-Dev, I did the following experiement. Posted in the form of a multi-part puzzle. Answer to the first part (with code) tomorrow. […]
I have CREATE TABLE privilege but can’t create tables
Posted on: November 10, 2004 8:57 pm
More on user-schema separation. In SQL 2000 and previous versions, granting someone CREATE TABLE privilege meant that they could create tables (no surprise there). The tables were "named after them" (e.g. bob.sometable) unless they were DBO. Because of user-schema separation in SQL Server 2005, that's no longer accurate. I always explain this in terms of […]
Users, Schemas, Objects, and Owners
Posted on: November 6, 2004 7:51 am
Separation of users and schemas is another cool SQL Server 2005 feature, but it has some interesting behaviors that folks may have to get used to. One is database object resolution, another is ownership chains. Say I have a user FRED. FRED is the owner of a schema named FRED FRED is a memeber of […]
