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
asked Sep 17 '10 at 02:08 PM in Default
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),
answered Sep 17 '10 at 04:59 PM
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
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.
answered Sep 17 '10 at 03:38 PM