Separation of users and schemas

Learn about separation of users and schemas in SQL Server 2005 in this excerpt from "A First Look at SQL Server 2005 for Developers."

This Content Component encountered an error

A First Look at SQL Server 2005 for Developers The following excerpt, courtesy of Addison-Wesley, is from Chapter 6 of the book "A First Look at SQL Server 2005 for Developers" written by Bob Beauchemin, Niels Berglund and Dan Sullivan. Click for the complete book excerpt series or purchase the book.



Separation of users and schemas

SQL-99 defines the concept of a database schema as a named group of data that is owned by a particular authorization ID. Schemas are scoped to the database (called "catalog" in SQL:1999), and one database can contain one or more schemas. Schema objects, such tables, views, and stored procedures, live in a schema, and the two-part name of a database object is actually schemaname.objectname.

Prior to SQL Server 2005, the concept of a schema was tied to a particular user. Any objects created by a user were owned by that user, and SQL Server really defined the two-part name of a database object as ownername.objectname rather than schemaname.objectname. There was a CREATE SCHEMA DDL statement, but you did not have the option of naming your schema, only its owner.

-- SQL Server 2000 create schema, no schema name
CREATE SCHEMA AUTHORIZATION fred
   GRANT SELECT ON v1 TO public
   CREATE VIEW v1 AS SELECT au_id, au_lname FROM authors
GO

-- SQL Server 2005 create schema with name
CREATE SCHEMA fredstuff AUTHORIZATION fred

This pre–SQL Server 2005 CREATE SCHEMA statement actually was a convenient way to create objects that belonged to a specific user (like fred, in this case) and grant permissions to them in a single DDL statement batch. The problem of having database objects tied to a particular user was that in order to drop the user, the database administrator had to reassign or drop and re-create all of that user's database objects.

SQL Server 2005 introduces the concept of named schemas as separate from users. When you use the new CREATE USER DDL to statement to create a user, you can assign a default schema for that user. If a default schema is not assigned, the DBO (database owner) schema is the default.

-- user's default schema is uschema
CREATE USER u1 FOR USER u1WITH, DEFAULT_SCHEMA = 'uschema'
go

-- user's default schema is dbo
CREATE USER u2 FOR LOGIN u2
go

A schema can be owned not only by a specific user (created with a SQL Server login or Windows login), but also by a Windows group, a database role, or an application role defined in that database. The new CREATE APPLICATION ROLE DDLstatement permits assignment of a default schema, but because many users can be assigned to a role (an ordinary role, not an application role), CREATE ROLE does not assign a default schema for the role. Note that the legacy procedures sp_adduser and sp_addapprole have been changed to first create a schema with the same name of the user or application role and then call the appropriate CREATE statement, specifying that schema as the default schema. Use of the new CREATE statements is preferred; the behavior of the stored procedures is kept only for backward compatibility.

The owner of a schema (a single user or multiple users) can create database objects within that schema and also grant schema-level privileges to others. The schema owner does have to be granted permission to create the database objects, but the grant permission exists on a database level, not on a schema level. Here's an example of a user that has an associated schema and is also the owner of that schema.

USE demo1
GO

CREATE LOGIN alogin1 WITH password = 'password1',
 DEFAULT_DATABASE = demo1
GO

-- default named schema
CREATE USER auser1 FOR LOGIN alogin1
 WITH DEFAULT_SCHEMA = aschema1
GO

CREATE SCHEMA aschema1 AUTHORIZATION auser1
GO

GRANT CREATE TABLE TO auser1
GO

SETUSER 'auser1'
GO

-- this works and creates aschema1.table1
CREATE TABLE table1 (theid INTEGER)
go

In this case, if we did not set a default schema for the auser1 user, his default schema would be dbo. Because auser1 is not a member of the dbo database role, the CREATE TABLE statement would fail.

What this means to the database administrator is that because schemas (and the objects they contain) can be owned by a role, an application role, or a Windows group, when a user is dropped from the database, the database objects she has have created do not have to be reassigned or dropped and re-created. Here's an example using a SQL Server role for a payroll system. We'll assume that a role called payroll has already been created.

USE payrolldb
GO

CREATE LOGIN janet WITH PASSWORD = 'temppwd',
 DEFAULT_DATABASE = payrolldb
GO

— default named schema
CREATE USER janet FOR LOGIN janet
 WITH DEFAULT_SCHEMA = prschema
GO

CREATE ROLE payroll -- if it does not exist GO

sp_addrolemember 'payroll', 'janet'

CREATE SCHEMA prschema AUTHORIZATION payroll
GO

GRANT CREATE TABLE TO janet
GO

Now, user janet can create tables, and they will be contained within the prschema schema. If Janet is reassigned, the user janet can be dropped from the database without affecting any of the tables she has created.

Having named schemas affects the way database object names are resolved. If Janet issues the SQL statement SELECT * FROM benefits, SQL Server will attempt to resolve the table name benefits in this order:

1. prschema.benefits (using the default schema)
2. dbo.benefits
3. sys.benefits

One further special case needs to be mentioned. It is possible that a database user will have a default schema that she does not own (such as dbo), but will have the ability to create database objects in a different schema. In that case, the database object in the CREATE DDL statement must explicitly use the two-part name. For example, if user janet was defined without a default schema keyword, her default schema would be dbo, since she is not a member of the dbo role.

— this statement would fail
CREATE TABLE benefits2003 (empid INT) — other columns elided

— this statement would succeed
CREATE TABLE prschema.benefits2003 (empid INT)

Schemas have their own sets of permissions. You can grant or deny permissions like SELECT, EXECUTE, or VIEW DEFINITION on a schema-wide basis. The following SQL statement prohibits the group public from seeing any database objects in the bob schema using the system views.

DENY VIEW DEFINITION ON schema::bob TO public

Synonyms

SQL Server 2005 introduces support for a database object known as a synonym. A synonym is just an alternate name for an existing database object that keeps a database user (more likely, a database programmer) from having to use a multipart name for an object. Synonyms can be defined on a two-part, three-part, or four-part SQL Server object name. A synonym can be defined by the following database objects:

  • Table

  • View

  • Stored procedure

  • User-defined function

  • Extended stored procedure

  • Replication filter procedure

Although synonyms can be created on a multipart object name, they are scoped to the database that they are created in. Here are some examples of creating and using synonyms.

USE pubs
GO

CREATE SYNONYM customers_east FOR eastserver.northwind.dbo.customers
GO

CREATE SYNONYM employees FOR payroll.employees
GO

-- these work
SELECT * FROM customers_east
SELECT * FROM employees
GO

USE northwind
GO

-- so does this
SELECT * FROM pubs..customers_east

Click for the next excerpt in this series: Specifying execution context for procedural code


Click for the book excerpt series or visit here to obtain the complete book.

Dig deeper on SQL Server Database Modeling and Design

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close