LearnNowOnline

Blog

SQL 2012 Default Schemas for Users

Excerpt by Don Kiely | June 04, 2013

SQL Server doesn't automatically create a schema with the same name as the user when you create a user. Instead you have to explicitly create a schema, assign ownership to a user, then create and add objects to that schema. You can (and usually should) assign a default schema to a user so that all objects the user creates-and doesn't explicitly assign to another schema-become part of the default schema.

The following code shows how this works. After creating the DefaultSchema database and changing the database context to it, the code creates the login carol, maps it to the user carol in the database, and grants it the ability to create tables. It then changes the execution context to the new user carol.

CREATE LOGIN carol WITH PASSWORD = 'crolPWD123%%%';

CREATE USER carol FOR LOGIN carol;

GRANT CREATE TABLE TO carol;

EXECUTE AS LOGIN = 'carol';

The code next attempts to create a new table1. But when the code created carol it didn't assign a default schema. SQL Server attempts to use the dbo schema, which is the default fallback schema. But Carol doesn't have ownership rights in the database so she can't create objects in the dbo schema.

CREATE TABLE table1 (tID int);

Since carol doesn't have the needed permissions the CREATE TABLE statement fails with this error message. In this case the problem of the two suggested is that carol doesn't have permissions.

Msg 2760, Level 16, State 1, Line 1

The specified schema name "dbo" either does not exist or

you do not have permission to use it.

After reverting to the original admin login that started this session, the code creates a schema and gives ownership to user carol. You'll see the AUTHORIZATION clause a lot in SQL Server because it lets you assign ownership in the same statement that creates or alters an object.

CREATE SCHEMA carolSchema AUTHORIZATION carol;

The code then once again changes the execution context to carol and attempts again to create table1. But, damn, it fails again! The problem now is that just because a user owns a schema doesn't mean that it's the user's default schema. A user could own hundreds of schemas and SQL Server shouldn't be responsible for picking one to be the default. But what does finally work is creating the table to be explicitly contained within the schema. The following statement explicitly creates table1 in the carolSchema, which finally works.

CREATE TABLE carolSchema.table1 (tID int);

Success at last!!!

This post is an excerpt from the online courseware for our SQL Server 2012: Security Fundamentals course written by expert Don Kiely.