LearnNowOnline

Blog

SQL 2012 Roles, Permissions, and Schemas

Excerpt by Don Kiely | May 31, 2013

The relationship of roles, permissions, and schemas is an important security concept in SQL Server. A fully qualified database object name consists of four parts:

server.database.schema.object

Usually you'll just need to refer to objects in the current database context by using the schema and object name. A schema is a collection of objects, such as tables and code modules, as shown in the Figure below. This method simplifies user management, particularly when you have to change ownership of objects. But more importantly, it simplifies permissions management.

You can assign permissions on a schema that apply to all objects in the schema. For example, if you assign SELECT permission on CarolSchema to a principal, all three tables in that schema have that permission. Setting permissions individually on objects is always an option, but if you've designed the schemas in a database well, in some sort of functional categories that make sense for the database, you can set permissions on the schema and have them apply to dozens if not hundreds of objects. Best of all, the permissions you assign apply automatically to any future objects you add to the schema. Continuing the SELECT example, if a year from now you add Table4 to CarolSchema, all principals with SELECT permission on the schema automatically have that permission on the new table. Multiple users and roles can have the same default schema, and if a principal has no default schema set, SQL Server attempts to find the object in the dbo schema.

This post is an excerpt from the online courseware for our SQL Server 2012: Security Fundamentals course written by expert Don Kiely.