This is one of those frequently ignored best practices.

When you create an object, specify the schema where it will be created.

I should also point out

When you reference an object (in a query for example), specify it’s schema.

I know I have a tendency to forget this myself. (Who am I kidding, I almost never follow this one.)

You want to do this:

CREATE TABLE dbo.TableName (Col1 int)

Not this:

CREATE TABLE TableName (Col1 int)

But why? I mean the table gets created either way and since the default is dbo there is no real reason to name it.

Actually no. The default is not in fact dbo. It frequently is dbo but by no means always. The default schema is part of your USER information. Specifically the DEFAULT_SCHEMA option. Well, unless you are a sysadmin. Then it actually does always default to dbo.

Here is where things get really interesting. Per BOL

Implicit Schema and User Creation

In some cases a user can use a database without having a database user account (a database principal in the database). This can happen in the following situations:

• A login has CONTROL SERVER privileges.
• A Windows user does not have an individual database user account (a database principal in the database), but accesses a database as a member of a Windows group which has a database user account (a database principal for the Windows group).

 

When a user without a database user account creates an object without specifying an existing schema, a database principal and default schema will be automatically created in the database for that user. The created database principal and schema will have the same name as the name that user used when connecting to SQL Server (the SQL Server authentication login name or the Windows user name).

In other words if you have access to create an object, but don’t have a USER in that database, then try to create an object without specifying a schema the following is going to happen:

1. A USER will be created for you.
2. A SCHEMA will be created under your user name.
3. The SCHEMA will be the default schema of your USER.
4. The object is created under your new SCHEMA.

And any subsequent objects will also be created under your SCHEMA.

So how do I know this can be a problem? I ran a vendor upgrade script recently. And …

• Our primary accounts had the sysadmin permissions taken away.
• The script I was given didn’t have the schema specified (at least for some of the objects).

I then spent several days figuring out why some of the objects where created in the wrong schema. I ended up restoring the database back to right before I started, fixing my default schema, and then re-running the script.

And now for the obligatory demo

I’ve already set up windows users for myself but you can use the commands here to create your own.

Note: Dopey the AD user is a member of the AD group Dwarf.

CREATE LOGIN [Kenneth-Laptop\Dwarf] FROM WINDOWS;
GO
USE Test;
GO
CREATE USER [Kenneth-Laptop\Dwarf] FROM LOGIN [Kenneth-Laptop\Dwarf];
GRANT ALTER ON SCHEMA::dbo TO [Kenneth-Laptop\Dwarf];
GRANT CREATE PROCEDURE TO [Kenneth-Laptop\Dwarf];
GO
EXEC sp_DBPermissions 'Test','Dwarf';
GO

EXEC sp_DBPermissions 'Test','Dopey';

-- Run the code as Dopey
EXECUTE AS LOGIN = 'Kenneth-Laptop\Dopey';
GO
USE Test;
GO
-- Create the stored procedure
CREATE PROCEDURE NoSchema AS
SELECT * FROM sys.tables;
GO
REVERT;
GO

-- Let's see what happened.
SELECT * FROM INFORMATION_SCHEMA.ROUTINES 
WHERE SPECIFIC_NAME = 'NoSchema';
GO
EXEC sp_DBPermissions 'Test','Dopey';
GO

Accidents happen so the last thing I should probably point out is fixing the mistake.

The most obvious fix is to delete the “bad” object and re-create it using the schema you intended (not always dbo). Unfortunately there are a few risks there. Any individual permissions, and/or data (if the object is a table) will be lost (or at least need to be re-created). The better solution is to keep the existing object but change the schema.

ALTER SCHEMA dbo TRANSFER [Kenneth-Laptop\Dopey].NoSchema;
GO
SELECT * FROM INFORMATION_SCHEMA.ROUTINES 
WHERE SPECIFIC_NAME = 'NoSchema';
GO

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: microsoft sql server, T-SQL