A couple of weeks ago I did a post on granting or denying permissions to all the tables within a database. However sometimes you don’t want to grant permissions to the whole database at once. This is still pretty easy but there are no built-in roles to do it. There are still two options; granting permissions to the table(s) themselves or granting permissions to one or more schemas.

Granting permission to a schema

This is similar to granting permissions to the database. Every table/view owned by the schema is affected and if you only use dbo then it’s exactly the same as granting permission to the database. If however you use multiple schemas you can grant permissions at a somewhat more granular level. For example granting the accounting department read(SELECT)/write(INSERT, UPDATE, DELETE) access to the Account schema and the HR department read/write access to the Employee schema. The other benefit is that as a “global” grant (affecting everything under the schema) every time you add a table or view the permissions are extended to the new object. I particularly like using this with the EXECUTE permission. Once I have granted EXECUTE to a schema then that group/role now can execute any new stored procedures or functions.

Granting permission to an individual object

You can grant any appropriate permission to an individual object. For example SELECT to a table or view and EXECUTE to a stored procedure or function. Generally this is the most granular permissions I bother granting. It’s possible to grant permissions at a column level but I’ve found a reason to do it. Typically I grant permissions at the object level I have particular table that need special handling. A table containing employee payroll information for example. You can move these into their own database or schema but sometimes that isn’t the most efficient option.

If you decide to use this method I HIGHLY recommend creating a role and granting the permissions to the role. Then granting that role to the individual or group who need it. This has two major advantages. If you name the role something obvious (EmployeePayroll for example) then its fairly obvious what needs to be granted for someone to be able to perform a specific job. Also, and even more important in my opinion, if you need to grant permissions to more than one or two objects it’s far easier to add them to a role than to remember what permissions needed to be granted. For example you determine that a job requires SELECT permission on 5 tables, INSERT and UPDATE on 3 and DELETE on 1 and you then grant those permissions to an individual. Six months later he decides to leave and you need to grant permissions to his replacement. How hard would it be to forget one of those grants? How easy would it have been to have removed the old person from a role and add a new one? Of course I’m ignoring the fact that generally you want to use AD groups and that avoids the problem all together but I still feel it’s a valid point.

Remember in general that granting permissions at the right level (database, schema, object) is important. This ties directly into the rule of least privilege and balancing it against continence and ease of use. It’s very easy to grant read/write to the entire database and sometimes it’s even appropriate. Keep in mind however, that it isn’t always. Sometimes you need to break your permissions down at a more granular level.

Filed under: Microsoft SQL Server, Security, SQLServerPedia Syndication Tagged: microsoft sql server, object permissions, schema permissions, security