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:


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.



Thumbnail for 637


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


Don Kiely

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor, and consultant specializing in Microsoft technologies. Don has many years of teaching experience, is the author or co-author of several programming books, and has spoken at many industry conferences and user groups. In addition, Don is a consultant for a variety of companies that develop distributed applications for public and private organizations.

This course excerpt was originally posted May 31, 2013 from the online courseware SQL Server 2012, Part 5 of 9: Security Basics by Don Kiely