In my Are you ready for SQL Server 2012 or are you still partying like it is 1999? post, I wrote about how you should start using SQL Server 2005 and SQL Server 2008 functionality now in order to prepare for SQL Server 2012. I still see tons of code that is written in the pre 2005 style and people still keep using those functions, procs and statements even though SQL Server 2005 and 2008 have much better functionality.

In today's post I will cover schemas. Schemas were introduced in SQL Server 2005, each schema is basically a distinct namespace in a database. A schema exists independently of the database user who created it. A schema is simply a container of objects. The owner of a schema can be any user, the ownership of the schema is transferable.

Let's see how this all works, first create a new login name Denis with a highly secure password

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
USE master
GO
CREATE LOGIN Denis WITH PASSWORD = 'Bla'
GO

To run all this code correctly, you should have two connections to the database we will create, one connection should be your admin connection, the other connection should be connected as this new user we just created.

Now create a new database named SalesStuff

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
CREATE DATABASE SalesStuff
GO

Inside the SalesStuff database create a new user which is mapped to the login Denis

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
USE SalesStuff
GO
CREATE USER Denis FOR LOGIN Denis
GO

Create a schema in the SalesStuff database named Sales, also create a table named Orders in that schema

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
CREATE SCHEMA Sales
GO
CREATE TABLE Sales.Orders
(OrderID int, OrderDate date, OrderAmount decimal(30,2))

Now login to the database with the Denis account and run the query below

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
select * from orders

You should see the following error.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'orders'.

The problem is that when you login, your default schema is not Sales and so the Orders table can't be found. Prefix the table with the schema and try again

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
select * from Sales.Orders

You get this error message
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'Orders', database 'SalesStuff', schema 'Sales'.

We need to give the Denis user select permissions for this table. Login as the admin and run the query below

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
GRANT SELECT ON SCHEMA::Sales TO Denis

That query gave the user Denis select permissions on all tables in the Sales schema. Notice the double colon syntax, that is how you need to grant, deny and revoke permissions. If you run the select query again, you will get back an empty resultset.

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
select * from Sales.Orders

Let's try to do an insert

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
insert Sales.Orders values(1,getdate(),100)

As expected, that fails also

Msg 229, Level 14, State 5, Line 1
The INSERT permission was denied on the object 'Orders', database 'SalesStuff', schema 'Sales'.

Go back to the admin query window, run the query below to give the insert permissions

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
GRANT INSERT ON SCHEMA::Sales TO Denis

If you try the insert again, it will succeed

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
insert Sales.Orders values(1,getdate(),100)

Remember how we tried to select from the table without specifying the schema? Let's try that again

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
select * from Orders

Msg 208, Level 16, State 1, Line 1
Invalid object name 'Orders'.

Same error, let's fix that

Go back to the admin query window and execute the query below

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
ALTER USER Denis
WITH DEFAULT_SCHEMA = Sales

We just made the Sales schema the default schema for the user Denis. Now if we specify the schema or if we omit the schema, we get back the same result

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
select * from Orders
select * from Sales.Orders

Go back to the admin connection and create this stored procedure

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
create procedure Sales.prtest1
as
select 1

Go to the query window for the user Denis and run the proc

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
exec prtest1

Msg 229, Level 14, State 5, Procedure prtest1, Line 1
The EXECUTE permission was denied on the object 'prtest1', database 'SalesStuff', schema 'dbo'.

As you can see, we don't have execute permissions for the stored procedure.
Bring up the admin query window and give Denis execute permissions on the schema

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
GRANT execute ON SCHEMA::Sales TO Denis

Now if you try to execute the proc from the connection which is logged in as Denis it succeeds

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
exec prtest1

Go back yet again to the admin query window and create another stored procedure

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
create procedure Sales.prtest2
as
select 2

Now if you go back to the connection for user Denis and execute the proc we just created, it also is successful.

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
exec prtest2

As you can see, once you have execute permissions on a schema, you don't have to go and explicitly give execute permissions for every stored procedure

To see all the tables that you have select permissions on, you can run the query below from the connection logged in as Denis. It will return 1 if you have select permissions or 0 if you don't

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
SELECT HAS_PERMS_BY_NAME
(QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name),
    'OBJECT', 'SELECT') AS have_select, name FROM sys.tables

Output
---------------
1	Orders

For procs it will return 1 if you have execute permissions, if you don't have execute permissions then the proc is not returned. Run the query below from the connection logged in as Denis

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
SELECT HAS_PERMS_BY_NAME
(QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name),
    'OBJECT', 'exec') AS have_select, name FROM sys.procedures

Output

---------------
1	prtest1

1	prtest2

As you can see you get 2 rows back

No go back to the admin connection and deny execute on the schema

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
DENY EXECUTE ON SCHEMA::Sales TO Denis

Run the query below from the connection logged in as Denis

TSQL
LINE NUMBER OFF | HIDE | SELECT ALL
SELECT HAS_PERMS_BY_NAME
(QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name),
    'OBJECT', 'exec') AS have_select, name FROM sys.procedures

As you can see nothing is returned at all

So what is so cool about schemas anyway?
When you start using schemas, you have a way to logically group a bunch of objects together. For example if you have a Sales and a Marketing schema then if you need to find a specific table that has something to do with Sales, you don't have to look up and down in object explorer to find the table, it should be sorted under the sales schema. Permissions are also easier, you give the sales person permission to the Sales schema and if new tables are added he or she will have the select permission the moment the table is created.
When using schemas you now can have a table named Customers in both schemas without a problem and each will hold data just for the department that uses the schema the table is in.

Read more
This was just a small overview, I did not cover all the things you need to know about schemas in SQL Server. Take a look at SQL Server Best Practices – Implementation of Database Object Schemas to get some more details about how to use schemas.

参考：http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/MSSQLServer/sql-advent-2011-day-4

【版权声明】
本站部分内容来源于互联网，本站不拥有所有权，不承担相关法律责任。如果发现本站有侵权的内容，欢迎发送邮件至masing@13sy.com 举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。