I have a stored procedure that:

  • checks if a table exists and if so, drops it.
  • creates that table again
  • then fills that table with about 30 queries.

When I (DB Owner) run this proc, everything works as intended. When my co-worker does so, who has DROP/CREATE rights on this database through a role in the Active Directory, several things go wrong. What boggles my mind is this:

The creation of the table did not have the dbo schema explicitly specified before the name. This resulted in a table called domain\cowork_id.table_name_here table to be created. Besides that table being created in his personal schema, he now also has that schema on the database (it didn't exsist prior to running the proc).

What happened? Why does SQL Server create tables in user's schema's instead of in dbo when that isn't specified?

up vote 26 down vote accepted

As a rule, you should explicitely specify dbo schema if you want to create the object in this schema.

As you are db_owner, your default schema is dbo, so there is no problem when you don't specify dbo schema while creating objects. But for other (Windows) users it's not the same.

Your users are members of Windows group that has no default schema. In this case the corresponding user and the schema are created when the user creates any object, it's documented here: CREATE SCHEMA (Transact-SQL)

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).

This behavior is necessary to allow users that are based on Windows groups to create and own objects. However it can result in the unintentional creation of schemas and users. To avoid implicitly creating users and schemas, whenever possible explicitly create database principals and assign a default schema. Or explicitly state an existing schema when creating objects in a database, using two or three-part object names.

To resolve the issue just assign dbo schema as default schema to all yor users-Windows groups or write the schema explicitely when creating objects. Always.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.