November 25, 2008 at 2:18 pm
It is my understanding that the default behavior when creating a table in SQL 2005 is that it will be created with dbo as the owner of the table. Is there a way to change this default behavior so that all tables get created as the user instead of as dbo?
I am working on moving an application from SQL 2000 to SQL 2005 and much of the logic within the application makes the assumption that the default behavior is to create a table with the user as the owner.
November 25, 2008 at 2:43 pm
Well, first - your understanding is incorrect. Object are not created under the 'dbo' schema by default. Objects are going to be created (by default) in the users default schema.
Now, if all users have the default schema of 'dbo' - then, all objects would be created 'by default' in the 'dbo' schema.
However, this all depends upon how you are creating your objects. If you are not specifically identifying the schema to be used - then you are going to rely on the defaults setup. I would highly recommend that you NOT use the default settings for creating objects.
When you create objects you should always specify the schema that the object belongs to. Schema qualifying object names becomes more critical in SQL Server 2005 and greater. In fact, you should also make sure all references are using two-part naming (at least).
And finally (for now) - notice that I did not say 'user' in the above at all. In SQL Server 2005 users and schemas have been separated. Users no longer own objects directly - users are the owners of a schema and objects belong to the schema. Well, you can always argue that a schema is an object if want 😀
My recommendation - as I stated above, is to not rely on the defaults defined and always specify the schema in all operations. That is:
SELECT ... FROM schema.Object
CREATE PROCEDURE schema.MyProcedureName AS ...
EXECUTE schema.MyProcedureName ...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 25, 2008 at 3:47 pm
Thanks, Jeffrey. That cleared things up for me nicely.
November 25, 2008 at 4:35 pm
Actually Jeff, the Owner of a SQL object can be different from it's Schema. Check out the ALTER AUTHORIZATION statement to see this..
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 25, 2008 at 10:34 pm
rbarryyoung (11/25/2008)
Actually Jeff, the Owner of a SQL object can be different from it's Schema. Check out the ALTER AUTHORIZATION statement to see this..
Not sure where you got the idea that I stated anything contrary to the above. If I did - could you please point it out?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 25, 2008 at 10:59 pm
Jeffrey Williams (11/25/2008)
rbarryyoung (11/25/2008)
Actually Jeff, the Owner of a SQL object can be different from it's Schema. Check out the ALTER AUTHORIZATION statement to see this..Not sure where you got the idea that I stated anything contrary to the above. If I did - could you please point it out?
Here:
Jeffrey Williams (11/25/2008)
Users no longer own objects directly - users are the owners of a schema and objects belong to the schema.
As I said, an objects schema and it's owner can be different, and thus an object still belongs to it's Owner, which is usually, but not necessarily the same as its schema.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 26, 2008 at 5:43 am
This sort of implies that you're only using the GUI to create tables. You can change the schema for the object created in the gui by accessing the properties window, but I'm finding, more and more, that creating all the objects through scripts works better and there, it's very easy to create an object to the correct schema:
CREATE myschema.mytable...
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 27, 2008 at 8:16 am
First off, WHY do you "need" (??) to create tables and set table ownership ?
Could you not just use a common table with an extra field like "OwnerID"to keep the data separate for each user and query with a [font="Courier New"]SELECT FROM dbo.CommonTable WHERE OwnerID =[/font] ...
Is this an actual NEED or just a way of achieving another goal, the real need ?
November 27, 2008 at 8:21 am
J (11/27/2008)
First off, WHY do you "need" (??) to create tables and set table ownership ?Could you not just use a common table with an extra field like "OwnerID"to keep the data separate for each user and query with a [font="Courier New"]SELECT FROM dbo.CommonTable WHERE OwnerID =[/font] ...
Is this an actual NEED or just a way of achieving another goal, the real need ?
Seperating out data structures by schema allows for different security settings on different schema's, different storage settings, logical seperation of processing and storage... all kinds of stuff. It's not actually meant as a Joe owns one table, Hazel the next, Achmed another one, Yuting one more. It's meant to break down more along the lines that AdventureWorks uses in the 2005 and 2008 iterations, Sales, Marketing, Production, etc.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 27, 2008 at 8:55 am
So, the actual need was security and acces privileges.
OK, I was not aware as to where Adventure Works was headed in SS2K8 ...
Learning something every day...
I am now marrying an application to another package which, for each company, create a new separate database dedicated to the company (10 companies, 10 databases with the same structure). ... Complicates things a bit on my side.
Regards
November 27, 2008 at 11:03 am
J (11/27/2008)
Could you not just use a common table with an extra field like "OwnerID"to keep the data separate for each user and query with a [font="Courier New"]SELECT FROM dbo.CommonTable WHERE OwnerID =[/font] ...
What you are really talking about is "Row-level security", which as a way of securing what is called "multi-tenant" databases and applications. Multi-tenancy has some specific requirements including that all tenants have the same schema/design, which is not stated or implied by the OP's post.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 28, 2008 at 6:28 am
Yikes! I'd say so. Upgrades to the data model must be entertaining.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 28, 2008 at 7:20 am
it's not too bad, Grant. The presumption is that all tenants are using the same software and schema, just that every data row is tagged with a Tenant ID and every access has to be filtered based on it. the filtering can easily be made bulletproof through the use of a universal layer of Views, and (owner)Schema security that forces all access to the tables through those views. (Hmm, maybe I should write an article on this ...) 🙂
Anyway, (design)schema upgrades are only slightly more complicated than a single-tenant environment.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 28, 2008 at 2:31 pm
rbarryyoung (11/28/2008)
it's not too bad, Grant. The presumption is that all tenants are using the same software and schema, just that every data row is tagged with a Tenant ID and every access has to be filtered based on it. the filtering can easily be made bulletproof through the use of a universal layer of Views, and (owner)Schema security that forces all access to the tables through those views. (Hmm, maybe I should write an article on this ...) 🙂Anyway, (design)schema upgrades are only slightly more complicated than a single-tenant environment.
[tapping foot] Waiting.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 29, 2008 at 7:14 am
rbarryyoung (11/28/2008)
(Hmm, maybe I should write an article on this ...) 🙂
{insert Jeapordy theme song here} 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply