SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Worst Practice - Not Qualifying Objects With The Owner

By Christoffer Hedgate,

When Andy recently added his latest piece in the Worst Practices series, about adding a column without thinking, it made me think about a worst practice I have been trying to combat for a long time. Maybe not as specific as most other worst practices, but one of the worst practices I often encounter is plain and simple laziness. One mistake that people often do just because they are lazy is to not qualify the names of objects with owner name. I will discuss this here, and in a later article I will discuss some other mistakes caused by laziness.

Using qualified names

Always remember to qualify object names with the owner of the object. This is good for performance for at least two reasons. The first is that when you specify an object name without qualifying it with owner name, SQL Server will first look for an object owned by the current logged on user. If not found, an object with the same name but owned by dbo will be used. So if a user named John is logged on to Northwind and executes SELECT OrderID FROM Orders, SQL Server will look for the object John.Orders in Northwind, and when not found dbo.Orders will be used. If he would have executed SELECT OrderID FROM dbo.Orders instead SQL Server would not have needed to look for an object named John.Orders. This might not affect performance in any notable way, but it is also important to avoid using an unexpected object. Imagine an application that logs on as this user John and executes a SELECT OrderID FROM Orders statement. This works fine until one day someone logged in as John decides to create a table called John.Orders (for whatever reason). Now the application will suddenly start returning OrderIDs from the new table John.Orders, and this bug might be pretty hard to find.

The other reason it is good for performance to qualify object names with owner name is that there is a much better chance of SQL Server reusing execution plans for statements written that way. For instance, if you are using sp_executesql to execute dynamic SQL statements you must actually fully qualify object names for SQL Server to reuse the execution plans. Fully qualifying an object name means specifying all four parts of it, i.e. server_name.database_name.owner_name.object_name. If you omit any of these a default will be used. Default server and database is of course the server and database where the query is executed, and default owner_name is as earlier specified dbo. But even though a default is used, SQL Server will not reuse the execution plan for statements specifying differently qualified object names. You can see this for yourself in SQL Profiler by running the following small script. Start Profiler and connect it to your server. Add all events in the Stored Procedures event class and remove all other events.

DECLARE @sql nvarchar(200)
DECLARE @params nvarchar(100)
DECLARE @intvar int

-- Create statement using fully qualified names
SET @sql = N'SELECT OrderID, CustomerID, EmployeeID FROM dbo.Orders WHERE EmployeeID = @empid'
SET @params = N'@empid int'
SET @intvar = 3
EXEC sp_executesql @sql, @params, @empid = @intvar

-- Execute the same statement again, reusing the previous execution plan
-- Note SP:ExecContextHit in Profiler
SET @intvar = 4
EXEC sp_executesql @sql, @params, @empid = @intvar

-- Same statement but not using fully qualified names
-- Note there is no SP:ExecContextHit in Profiler
SET @sql = N'SELECT OrderID, CustomerID, EmployeeID FROM Orders WHERE EmployeeID = @empid'
SET @params = N'@empid int'
SET @intvar = 4
EXEC sp_executesql @sql, @params, @empid = @intvar

When you run the script you should see an SP:CacheInsert when the first query is executed, but when it is executed again using only a changed value for @empid you will instead see an SP:ExecContextHit. This means that SQL Server could reuse the execution plan. Finally, when the third query is executed, you will again see an SP:CacheInsert, even though the result of the query is exactly the same as for the prior one. The reason is as I explained earlier that the second statement does not qualify the name for Orders in the same way as the first statement does.

Specifying the owner name is not just important when executing SELECT statements. It is also important, arguably even more important, to qualify objects with owner name when creating them. For instance, if John would create a table without specifying the owner of it he would become the owner himself. This means that any other user who wants to execute a SELECT statement against this table must qualify the table name with his owner name when SELECTing from it. If they would also always remember to qualify object names it might not be that much of a problem, but since we know people are lazy (or do not know what it means to qualify a name) they probably will not always do that. So when John creates his table his statement should begin with CREATE TABLE dbo.TableName ... There are of course exceptions when you really want to create a table with John as owner, but in those cases he should specify that, i.e. CREATE TABLE John.TableName ... Why is this important? Imagine that John creates this table in a database on a test server and saves the statement in a script. Later, when this table is to be created in the production database it might be someone else that executes this script, and the table will get a different owner.

It is even more important to qualify names of objects used inside a stored procedure. Otherwise objects that are not qualified with owner name and referenced in SELECT, INSERT, UPDATE and DELETE statements will default to the owner of the stored procedure, not the person executing the stored procedure. Again, these objects might be those you want, but it is always better to explicitly qualify the names. If the procedure is used as an access control mechanism for data in tables (i.e. direct access to tables is restricted and all users must use procedures that SELECT data from them) yu might run into another problem. If those object names for the tables that are restricted are not qualified in the procedure only the creator of the procedure will be allowed to access them through it.

Finally, if you do not qualify object names used in procedures with the statements CREATE/ALTER/DROP TABLE, TRUNCATE TABLE, CREATE/DROP INDEX, UPDATE STATISTICS and DBCC commands you might cause havoc in the system. The reason for this is that object names are resolved at run-time, using the name of the user that executes the procedure as the default owner for objects that are not fully qualified. Imagine that the user Jane has a table called Orders where she keeps her order info. Now she needs a quick and easy way to empty it so she creates a procedure like the one below:


She tries the procedure in a test environment and it works great, her Orders table gets truncated so she moves the procedure to the production environment. Then one day John needs to empty the data from Jane's Orders table, so he executes the procedure (by running EXEC Jane.TruncateOrders). Guess what happens? Well, if there is a table called John.Orders it would be truncated, and if there is not, then dbo.Orders will be truncated since dbo is the default that is used when SQL Server does not find an object owned by the current user.

So, for the reasons listed above I would recommend you to make it a habit to always specify all objects names using the syntax owner_name.object_name. And I am not just saying you should write your procedures this way, I want you to make it a habit to always do it, even for quick ad-hoc queries that you just execute once. If you make it a habit you will not forget it when it is really necessary and important.

Total article views: 16896 | Views in the last 30 days: 3
Related Articles

How to change object owner......

Change Object Owner


Tip: Schema Qualify Objects in SPs

“Eat your broccoli.” “Wear your gloves.” “Schema qualify your objects.” Your Mom wasn’t kidding, ...


Stored Procedure with non-qualified objects

Executing a USP cannot find users table from their default schema


Owner has to qualify?!

Hi! I am executing a stored procedure that is inserting into tblInfo. The owner of this table is Ap...