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
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
CREATE DATABASE SalesStuff
GO
Inside the SalesStuff database create a new user which is mapped to the login Denis
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
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
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
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
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.
select * from Sales.Orders
Let’s try to do an insert
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
GRANT INSERT ON SCHEMA::Sales TO Denis
If you try the insert again, it will succeed
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
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
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
select * from Orders
select * from Sales.Orders
Go back to the admin connection and create this stored procedure
create procedure Sales.prtest1
as
select 1
Go to the query window for the user Denis and run the proc
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
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
exec prtest1
Go back yet again to the admin query window and create another stored procedure
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.
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
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
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
DENY EXECUTE ON SCHEMA::Sales TO Denis
Run the query below from the connection logged in as Denis
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.
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
