Managing SQL Server database schemas

Introduction

Database schemas in SQL Server are another way that you can manage security.

A database schema is a way to logically group objects such as tables, views, stored procedures etc. Think of a schema as a container of objects. You can assign a user login permissions to a single schema so that the user can only access the objects they are authorized to access. Schemas can be created and altered in a database, and users can be granted access to a schema. A schema can be owned by any user, and schema ownership is transferable.

The default database schema is "dbo".

T-SQL Commands

To change owner of a schema:

ALTER AUTHORIZATION ON SCHEMA::Market To UserX

To move a table from one schema to another (in this case, to move the Address table from the person schema to the HumanResources schema):

ALTER SCHEMA HumanResources TRANSFER Person.Address;