“Eat your broccoli.”
“Wear your gloves.”
“Schema qualify your objects.”
Your Mom wasn’t kidding, and she always gave the best advice. We’ve already seen how it would have paid off for us to end all T-SQL statements with a semicolon, even though it’s not currently required – after all, don’t CTEs and MERGE statements have semicolon requirements? But it was always just easier not to bother with typing the schema with every object name. Easier, but not better.
SQL Server 2008 R2 Books Online says:
“Inside a stored procedure, object names used with statements (for example, SELECT or INSERT) that are not schema-qualified default to the schema of the stored procedure.”
Well, let’s just test that, shall we? This script creates a schema, two tables (one with the new schema, one with the default schema), and a stored procedure. The SP selects data from a non-schema-qualified table.
USE mydb
GO
CREATE SCHEMA JensTestSchema
GO
CREATE TABLE JensTestSchema.Table1 (id TINYINT, say VARCHAR(100));
CREATE TABLE dbo.Table1 (id TINYINT, say VARCHAR(100));
INSERT INTO JensTestSchema.Table1 (id, say) VALUES (1, ‘Selected from JensTestSchema.Table1′);
INSERT INTO dbo.Table1 (id, say) VALUES (1, ‘Selected from dbo.Table1′);
GO
CREATE PROCEDURE JensTestSchema.usp_test_schemas
AS
SELECT id, say FROM Table1;
RETURN 0
GO
exec JensTestSchema.usp_test_schemas
GO
What did we get? “1 Selected from JensTestSchema.Table1″, of course! We didn’t specify a schema, so the SELECT pulled data from the object with the same schema as the stored procedure.
Now let’s qualify that table object:
-- Now alter the SP so that the internally referenced table is schema qualified.
ALTER PROCEDURE JensTestSchema.usp_test_schemas
AS
SELECT id, say FROM dbo.Table1;
RETURN 0
GO
exec JensTestSchema.usp_test_schemas
Aaaand we get “1 Selected from dbo.Table1″.
Point proven. Now maybe you’ll listen to your mom, won’t you?
One more excellent reason to follow good coding practices: BOL says something else about unspecified schemas in SPs:
…access to those tables through the stored procedure is restricted by default to the creator of the procedure.
There’s nothing like a good, obscure permissions problem to really raise the blood pressure!
Happy days,
Jen McCown
