When should dbo be used?
-
Can someone give some sage advice on when to use 'dbo' in scripts, if ever? This system has many test systems that store data locally first, then send it to a central server after a test finishes. Users of course browse the data on the central. I will need to use some distributed SQL to transfer data from a test system to the central database.
In the past I've always created an account (e.g. TestSystem) that "owns" the database and use scripts to generate all the tables, triggers, stored procedures etc. This script is run from the TestSystem account. Similarly the central server database is created with an account (e.g. CentralSystem).
Then I create a few groups (e.g. TestUser, AdminUser) and grant appropriate table privileges to these few groups as needed. If individual user accounts are needed these are created and they get assigned to the groups they need based on privileges required to do their function.
With this approach (the script is always run the the db owner account) is there any benefit to prefixing owner names in a create table statement?
Is there any need ever to use 'dbo' in any scripts?
Thanks in advance.
Fred
-
I can't wait to hear some of these truly smart people answer your question. This generates quite a bit of thought from me, so I can only assume you will get some get input from others.
For my part, I can think of one instance in which the owner mattered quite a bit. I run some scripts to get a feel for table growth. I do not care about a Developers table size, I only care about dbo. tables in Production. There are some, (not many) Developers tables in Production and my script needs to discriminate, (can I still use that term?).
That would be one vote for using owner name prefixes.
I wasn't born stupid - I had to study.
-
I know that your question is quite long, but I'm still not sure what you are asking. I'm not sure if you are asking if objects should be owned by dbo or if it is important to use dbo.tableName in queries.
There is better performance if you qualify the tableName with the owner. For example, a table is owned by dbo and the user account is Sam. If the query doesn't specify dbo, then SQL will first look for a table owned by Sam before finding the table owned by dbo.
I'm sure some of the real guru's can explain it better than I, but hopefully that will make sense to you.
Kathi
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor -
There is an advantage to using dbo in the create object scripts. You might have a cause where you are logged in as a user that has the ability to create objects in the database but for whatever reason the user isn't the database owner and the login isn't a member of the sysadmin fixed server role. In that case the object won't get created as dbo but as the user. You can have this situation if the user is a member of either the db_ddladmin or db_owner fixed database role or if the user has been granted the right to create the object type.
As far as what Kathi says, she's exactly right. If the user attempting to access the object isn't dbo and the owner isn't specified in the query (e.g. SELECT * FROM authors), SQL Server will first attempt to find an object belonging to the user. Meaning in this case if we have a user named John, SQL Server will look for John.authors. This results in an SP:CacheMiss event and causes SQL Server to look again for the object. It'll find it at dbo.authors, but obviously this is an extra cycle we'd like to avoid. I talk about this a bit in the Stored Procedures and Caching article I wrote. The article is partially based on the following Microsoft KB article:
INF: SQL Blocking Due to [[COMPILE]] Locks (263889)
K. Brian Kelley
@kbriankelley -
Jumping on the bandwagon here. This should be "best practice", but in the real world you amazingly seldom see the fully qualified name.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
In fact it is suggested you use the fully qualified name in every circumstance from CREATE statements to actual DML such as a select statement.
You actually get a slight performance (yes really small) in DML statements by explicitly defining the schema owner.
It would however be nice to maybe one day have a feature where all objects in a db can be set to only create under dbo or when doing a select dbo is always assumed.
-
maybe this helps : ( and the comments ! )
http://www.sqlservercentral.com/columnists/awarren/worstpracticesobjectsnotownedbydbo.asp
http://www.sqlservercentral.com/columnists/sjones/istheschemastillneeded.asp
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Thanks to all for their help. After reading the articles and everyone's suggestions it seems like there's just one rule to remember.
1) Always qualify every object with dbo whether creating objects or referencing them in DML statements.
I apologize for not running any test code but are there any issues with restricting permissions to users. It seems like I still want to create objects from my schema owner account ( e.g. TestSystem), grant appropriate privileges to groups. Any user granted to a group will only have permissions (e.g. select only ) granted to that group even though they will preface the object names with dbo, correct?
Does this make sense?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply