asked
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
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
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][1] 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. [1]:
http://technet.microsoft.com/en-us/library/ms190387.aspx [2]:
http://msdn.microsoft.com/en-us/library/ms176060.aspx
@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.
@Oleg, that would make sense and explain why I was having a hard time understanding his question.
+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**?
@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.
@Matt, no, I don't think I have. I can not think of a good reason to do it.
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),
+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...
