Disclaimer: This is work in progress based on notes gathered over the years, intended to consolidate information from the various sources.
Last updated: 15-Mar-2024
[SQL Server 2005] Schemas
- {def} a collection of database objects that are owned by a single user and form a single namespace
- a named container for database objects
- allows to group objects into separate namespaces
- collection of like objects which provide maintenance and security to those objects as a whole, without affecting objects within other schemas [1]
- reside within databases
- fulfilling a common purpose [1]
- each schema can contain zero or more data structures (aka objects) [1]
- all objects within a schema share
- a common naming context
- a common security context [10]
- behavior of schema changed
- ⇐ compared to SQL Server 2000
- schemas are no longer equivalent to database users
- each schema is a distinct namespace that exists independently of the database user who created it
- used as a prefix to the object name
- schema is simply a container of objects [3]
- code written for earlier releases of SQL Server may return incorrect results, if the code assumes that schemas are equivalent to database users [3]
- can be owned by any database principal
- this includes roles and application roles [3]
- its ownership is transferable [3]
- every object is contained by a schema [6]
- anything contained by it has the same owner [6]
- separation of ownership [3]
- ownership of schemas and schema-scoped securables is transferable [3]
- objects can be moved between schemas [3]
- a single schema can contain objects owned by multiple database users [3]
- multiple database users can share a single default schema [3]
- permissions on schemas and schema-contained securables can be managed with greater precision than in earlier releases [3]
- each user has a default schema [3]
- user’s default schema is used for name resolution during object creation or object reference [7]
- {warning} a user might not have permission to create objects in the dbo schema, even if that is the user’s default schema [7]
- when a login in the sysadmin role creates an object with a single part name, the schema is always dbo [7]
- a database user can be dropped without dropping objects in a corresponding schema [3]
- catalog views designed for earlier releases of SQL Server may return incorrect results
- ⇐ includes sysobjects
- more than 250 new catalog views were introduced to reflect the changes
- when creating a database object, if you specify a valid domain principal (user or group) as the object owner, the domain principal will be added to the database as a schema. The new schema will be owned by that domain principal [3]
- schema-qualified object name (aka two-part object name)
- if schema is omitted, a schema resolution is performed (aka implicit resolution)
- checks whether the object exists in the user's default schema
- if it doesn't, checks whether it exists in the dbo schema [5]
- extra costs are involved in resolving the object name (aka name resolution) [5]
- uses a spinlock [8]
- in rare occasions a spinlock could not be acquired immediately on such an operation
- this may occur on a system under significant load [8]
- the contention appears on the SOS_CACHESTORE spinlock type [8]
- {resolution} ensure that you always fully qualify your table names [8]
- if multiple objects with the same name exist in different schemas, the wrong object might be retrieved [5]
- improves readability
- {recommendation} always use two-part object names in queries (aka schema-qualify objects)
- {poor practice} partition data and objects by using only schemas
- ⇐ instead of creating multiple databases [1]
- {poor practice} complex schemas
- developing a row-based security schema for an entire database using dozens or hundreds of views can create maintenance issues [6]
- {benefit} simplify database object management
- groups of tables can be managed from a single point [4]
- by creation of categories of tables [4]
- helps navigation through database [4]
- allow control permissions at schema level
- {benefit} provide separation of ownership
- allows to manage user permissions at the schema level, and then enhance them or override them at the object level as appropriate [10]
- {recommendation} manage database object security by using ownership and permissions at the schema level [2]
- {recommendation} have distinct owners for schemas or use a user without a login as a schema owner [2]
- {recommendation} not all schemas should be owned by dbo [2]
- {recommendation} minimize the number of owners for each schema [2]
- {benefit} enhance security
- by minimizing the risk of SQL injection
- by assigning objects to schema it is possible to drop users without rewriting your applications as the name resolution is no longer depend upon the user or principals names
- used as an extra hierarchical layer for solution and security management [1]
- gives architects and developers the ability to choose between the types of logical separation of objects they have created, as well as benefit from having a combination of multiple databases and multiple schemas within them [1]
- {type} system schemas
- can't be dropped
- [default schema] dbo
- included in each database
- if an application needs to create objects in the under the dbo schema then by granting dbo privileges to the application [12]
- increases the attack surface of the application [12]
- increases the severity if the application is vulnerable to SQL Injection attacks [12]
- can be set and changed by using DEFAULT_SCHEMA option of [3]
- e.g. CREATE USER <user_name> WITH DEFAULT_SCHEMA = <schema_name>
- e.g. ALTER USER <user_name> WITH DEFAULT_SCHEMA = <schema_name>
- if DEFAULT_SCHEMA is left undefined, the database user will have dbo as its default schema [3]
- [SQL Server 2005] Windows Groups are not allowed to have this property [11]
- [SQL Server 2012] Windows Groups can also have a defined default schema [1]
- streamlines the process of creating users
- if no default schema is specified for a new user, instead is used the default schema of a group where the user is a member [9]
- {warning} not to be confused with the dbo role [6]
- INFORMATION_SCHEMA schema
- an internal, system table-independent view of the SQL Server metadata
- enable applications to work correctly although significant changes have been made to the underlying system tables
- guest schema
- sys schema
- provides a way to access all the system tables and views [7]
- {type} user-defined schemas
- {best practice} assign objects to user-defined schemas
- leaving everything in the dbo schema is like putting everything in the root directory of your hard drive [8]
- it saves the Query Processor a step from having to resolve the schema name out by itself [8]
- avoid ambiguity
- {best practice} assign each user a default schema
- ensures that if they create an object without specifying a schema, it will automatically go into their assigned container [8]
- {type} role-based schemas
- [SQL Server 2012] every fixed database role has a schema of the same name [7]
- {exception} public role5
- {action} create objects in schema
- {prerequisite}
- schema must exist
- the user creating the object must have permission to create the object, either directly or through role membership [7]
- the user creating the object must either [7]
- be the owner of the schema
- be a member of the role that owns the schema
- have ALTER rights on the schema
- have the ALTER ANY SCHEMA permission in the database
- {recommendation} group like objects together into the same schema [2]
- {operation} create schema
- {recommendation} use two-part names for database object creation and access [2]
- {operation} change schema (aka modify schema)
- when applying schema changes to an object and try to manipulate the object data in the same batch, SQL Server may not be aware of the schema changes yet and fail the data manipulation statement with a resolution error [5]
- the parsing does not check any object names or schemas because a schema may change by the time the statement executes [6]
- triggers a database lock
- invalidates existing query plans
- a new plan will need to be recompiled for the queries as soon as they are run anew
- not allowed on
- [SQL Server 2014] [memory-optimized tables]
- [table variables]
- {best practice} explicitly list column names in statements in case a schema changes
- {operation} schema dropping
- [windows groups]
- an exception in the SQL Server security model [11]
- a secondary identity with additional capabilities that are traditionally reserved only for primary identities [11]
- require handling not seen in any other security system [11]
- can simplify management but due to their hybrid nature, they come with some restrictions [11]
- {recommendation} for users mapped to Windows groups, try and limit each Windows user to one Windows group that has database access [2]
References:
[1] 40074A: Microsoft SQL Server 2014 for Oracle DBAs, Microsoft, 2015
[2] Bob Beauchemin et al (2012) SQL Server 2012 Security Best Practices - Operational and Administrative Tasks [whitepaper]
[3] MSDN (2005)User-Schema Separation [link]
[4] Solid Quality Learning (2007) Microsoft SQL Server 2005: Database Essentials Step by Step
[5] Itzik Ben-Gan (2008) Microsoft® SQL Server® 2008 T-SQL Fundamentals
[6] Adam Jorgensen et al (2012) Microsoft® SQL Server® 2012 Bible
[7] Kalen Delaney et al (2013) Microsoft SQL Server 2012 Internals
[8] Buck Woody (2009) SQL Server Best Practices: User-Defined Schemas [link obsolet]
[9] Microsoft (2014) 10977B: Updating Your SQL Server Skills to SQL Server 2014 (Trainer Handbook)
[10] Microsoft (2012) 20465B Designing Database Solutions for SQL Server 2012 (Trainer Handbook
[11] Laurentiu Cristofor (2008) SQL Server: Windows Groups, default schemas, and other properties [link]
[12] Dan Sellers's WebLog (2006) Post Webcast’s Notes: Securing SQL Server 2005 for Developers [link]
[13] Microsoft Learn (2024) SQL Server 2022: System Information Schema Views (Transact-SQL)
[14]
Acronyms:
SQL - Structured Query Language
SQL - Structured Query Language
