Schemas within SQL server allow for logical separation of data within a single database. A schema is an extra layer which can allow objects in the database – including tables, views, procedures, etc. – to be separated. They could be used simply to group objects together in a more user-friendly way or they can be utilised as the security feature which they are designed.
Creating a schema will require either the db_securityadmin role or CONTROL access to the database. Once we have that its a very straight forward task to create the schema:
CREATE SCHEMA Peanuts;
With that we’ve got a fresh new schema available to us to create new tables, views, whatever we fancy inside there. For now lets create a table to store some information, a procedure to retrieve it, and then insert some records:
CREATE TABLE Peanuts.Friends (
FriendName VARCHAR(20)
);
CREATE PROCEDURE Peanuts.GetFriends AS
BEGIN
SELECT FriendName
FROM Peanuts.Friends;
END;
INSERT INTO Peanuts.Friends (FriendName)
VALUES ('Linus'), ('Lucy'), ('Sally');
Now that we’ve got that as a baseline we’ve got 2 colleagues who want to use the data in there but in different ways. We have a good boy named Snoopy who maintains the Friends details, and we’ve got a lad named Charlie who likes to do some reporting on them.
By using the schema we can apply permissions to only the objects inside of it, separate to any other objects we might have in the same database. To start with these users only have data reader access into the database but lets apply the permissions specific to this new schema:
GRANT INSERT ON SCHEMA::Peanuts TO Snoopy;
GRANT EXECUTE ON SCHEMA::Peanuts TO Charlie;
I’ve found this approach to securing stored procedures particularly beneficial in reporting use cases. This is especially true if you use a reporting tool which executes under a service account and you want to restrict what it has access to across your environments rather than blanket access to read data or execute all procedures – or manage the headache of applying permissions to individual objects.
If we try the operations below being ran by both of our users we’ll now see differing results based on their permissions:
/* Try to add some data */
INSERT INTO Peanuts.Friends (FriendName)
VALUES ('Peppermint Patty');
/* Try to run a report */
EXECUTE Peanuts.GetFriends;
As we’ve restricted these operations they will each throw up appropriate exceptions, for example if Snoopy tries to run the report it’ll indicate the user doesn’t have permission to execute the procedure:
The EXECUTE permission was denied on the object 'GetFriends', database 'Blog', schema 'Peanuts'.
Another interesting way we can use schemas is by specifying the default schema for a user within the database. This will mean that any queries which request an object without a schema defined will first look for it in that schema. We can apply that to Charlie as below:
ALTER USER Charlie WITH DEFAULT_SCHEMA = Peanuts;
Once that’s set up for Charlie then he could connect into the database and just run the following for his reports:
EXECUTE GetFriends;
Although this is a trivial example, this could help to segregate parts of an application by moving objects into a specific schema and then setting the application service account to use that schema as a default. In this way the contents of the application data could be separated and secured from other elements in the same database.
There is quite an old but more verbose article on implementing schemas from Microsoft which is available here.
Personally when designing a database I like to make use schemas not only to support security – such as restricting access for reporting as in our example – but it helps provide a logical separation between functionality in the case of an application, or to separate different categories of data such as fact, dimension and staging tables within an analytical database.
Andy Brownsword 