x

Fully Qualified Table Name

I have a user that is both the dbo and schema owner of a table but yet can query the table without specifying the fully qualified name. For example select * from last_table fails but select * from userA.last_table succeeds even when ran as user1. Thanks
more ▼

asked Sep 17 '10 at 02:08 PM in Default

click55bum gravatar image

click55bum
21 1 1 1

FWIW, I was thinking about this over the weekend and wrote a DDL trigger to prevent objects being created that use non-qualified object references of any kind: That's [here][1].

[1]: http://www.atlantis-interactive.co.uk/blog/post/2010/09/20/Preventing-non-qualified-object-use-with-DDL-Triggers.aspx
Sep 20 '10 at 01:14 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

The correct answer is that you should ALWAYS use the two part naming convention as Timothy suggested but not just for the reason he stated.... there are also reasons for performance. When you use just the table name, rumor has it that SQL Server first looks to see if there's a table under the schema for the name of the user. If there is none, it supposedly looks in master next. When it doesn't find one there, then it finally looks in the current DB in the "dbo" schema. For batch jobs, that's not so bad. For GUI code, that represents a substantial amount of time.

Always always always use the 2 part naming convention. If 3 or 4 parts are required, then instantiate a couple of synonyms so you can get back down to the two part naming convention. That way if a database name or a linked server name changes, you don't need to find all the places in code to change it... you just need to change the synonym (or passthrough view),
more ▼

answered Sep 17 '10 at 04:59 PM

Jeff Moden gravatar image

Jeff Moden
1.7k 2 8

+1, especially about the part to do with synonyms to reduce the 3/4 part pain. Possibly worth mentioning that you can't schema bind through a synonym though...
Sep 18 '10 at 08:08 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

I am not entirely sure if I am following. Also, the answer will be affected by

You have a user called user1 that can select from userA.last_table despite not being either in the DBO group or being userA. If you are asking this in a permissions context, then this just means user1 from some method has read permission on that table. It could be granted through something like dbdatareader on the database, read permissions on userA schema, or permissions granted directly on the table. You may want to look at: http://technet.microsoft.com/en-us/library/ms190387.aspx

If you are asking why he needs to fully qualify it, this is simply because user1's default schema is not userA. Generally you can set the user's default schema using the Alter User command, but there are some exception if the user is mapped to a Windows Group, etc. You can look at: [http://msdn.microsoft.com/en-us/library/ms176060.aspx][2]

As a general rule, I recommend always schema qualifying table names. It can help avoid errors. I also generally recommend using meaningful schemas and separating the data out by them, but I know some (especially people who learned on SQL Server 2000 or earlier) tend to disagree with or at least disregard in practice that advice.

[2]: http://msdn.microsoft.com/en-us/library/ms176060.aspx
more ▼

answered Sep 17 '10 at 03:38 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 19 22 32

@TimothyAWiseman I think it is a simple typo, user1 should have been typed as userA. This way, the behaviour makes perfect sense and is by design. If the user in question owns the schema named userA, but is also a member of sa server role, such the user always comes in as a dbo when he logs in. Therefore, when this user runs select * from last_table, the query is guaranteed to fail, because the userA came in as dbo, and regardless of what other schemas he owns, the engine must assume that the request is to select * from dbo.last_table, and because such table does not exist, the query fails. The lesson is to always schema qualify every statement. Even if it is possible to have the parser resolve the names correctly, it is still silly to force the parser to waste its time and try to deduce the actual table name.
Sep 17 '10 at 04:47 PM Oleg
@Oleg, that would make sense and explain why I was having a hard time understanding his question.
Sep 18 '10 at 07:48 AM TimothyAWiseman
+1 - as an aside - have you ever seen anyone use the facility whereby an unqualified object reference is resolved to the calling user's default schema on purpose?
Sep 18 '10 at 08:12 AM Matt Whitfield ♦♦
@Timothy. No argument from me on what you stated about this particular problem. I just got off on the tangent of always specifying a 2 part name and the reasons why. Permissions are a separate issue that I wasn't addressing.
Sep 19 '10 at 10:35 AM Jeff Moden
@Matt, no, I don't think I have. I can not think of a good reason to do it.
Sep 19 '10 at 10:03 PM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x362
x82
x81

asked: Sep 17 '10 at 02:08 PM

Seen: 4426 times

Last Updated: Sep 17 '10 at 02:08 PM