SQL 101 : SQL 2005 Schemas

‘User Schema Separation’ is allegedly an ANSI SQL 1999 standard but documentation on ansi standards is not freely available on the web.

2000 : [server].[database].[owner].object

2005 : [server].[database].[schema].object

Prior to SQL 2005, each database object had 1 owner.

In SQL Server terminology, the concept of a Schema was introduced in SQL 2005 to provide a logical way to group database objects, ease administration and address security.

Benefits of SQL 2005 Schema addition >

Database Structure	Organization Subdivide database into areas of interest / business area by assigning tables with schema names. For example in the AdventureWorks database > HumanResources.Employee Person.Address Sales.CreditCard This is especially helpful in databases with a large number of tables,
Easier Assignment of permissions	Logical Permissions Grouping Permissions assigned at schema level make it easier to control relevant access to developers etc by subject area. Reduced Administration One command versus many when granting access to a set of objects when grouping them by schema.
Objects not reliant on users	Duplicate Objects In SQL 2000, objects being assigned to users means that 2 users could create objects with the same name which is confusing to say the least. Removing Users Objects not being directly tied to user accounts means that dropping user accounts is much easier. It can be accomplished without either dropping all the owned objects or changing the object owner for any linked objects. Users that OWN a schema now cannot be dropped however without changing the schema ownership.
Ease of teamworking	Multiple users can share a schema Multiple users can OWN a schema through a role or group membership. ‘Default Schema’ setting at user level prevents users having to explicitly reference a schema, for example – if they always use they same one.
Security	The addition of schemas means that combined with object permissions, many more levels of security can be achieved. Access to system objects can now be controlled by a user’s permissions on the SYS schema, unless of course their server role overrides this.
Script Order	Encompassing create statements for related objects inside the same schema makes scripting order unimportant. For example > CREATE SCHEMA UserInfo CREATE TABLE Logins ( UserId INT NOT NULL REFERENCES Users (UserId), LoginDate DATETIME ) CREATE TABLE Users ( UserId INT NOT NULL PRIMARY KEY ) GO

SQL 2000 / 2005 Underlying tables >

	2000 table	2005 tables
Server level	syslogins	sys.server_principals
Database level	sysusers	sys.database_principals sys.schemas