Professional Microsoft® SQL Server® 2008 Administration

9.6. Schemas

Schemas were a feature in SQL Server 2000 that weren't emphasized much, but as of SQL Server 2005, they are an integral part of the Database Engine. Schemas enable you to group database objects into a logical group for security, ease of use, and manageability. They provide the same functionality as namespaces. Whether you have created custom schemas or not, it's a best practice to use schema names in your queries. If you were to query the Salary table in the HumanResources schema, it might look like this:

SELECT FirstName, LastName, Salary, StartDate, EndDate FROM HumanResources.Salary

Using this as an example, we would assume that the HumanResources schema contains information that should be considered more secure than data in other schemas, given the nature of Human Resources information. In SQL Server 2008, you can allow a user to see all tables in every schema with the exception of tables in the HumanResources schema. You could also give a user a schema that the user owned and could create tables within without interfering with the other tables in the database. This is because SQL Server considers both the schema and the object name when referring to objects. The user could create tables inside the schema that would therefore be considered by SQL Server to be named differently. Actually, SQL Server considers them separate objects altogether. The table Employees in the MyUser schema is a different table than the Employees table in the YourUser schema. If a schema ...