“The SQL Guy” Post # 7: SQL Server Database Engine Permission Model–Part 1

This week we start the first of a three-part series on SQL Server database permissions and the permissions security model used by the database engine. In conversations with many individuals across the country, I have found that not all DBAs or prospective DBAs that I have talked to are always sure about how SQL Server security works and a common understanding may help clarify some things.

This week we start with the basics of understanding the three commands you use to configure SQL Server security and how they are appliedSQLServer in SQL Server. As always, if you want to test these out and don’t have SQL Server handy, you can download a full-featured evaluation copy of SQL Server 2008 R2 from the TechNet Evaluation Center at http://technet.microsoft.com/en-ca/evalcenter/default.aspx (look under Server Products and Technologies | Release). If you are feeling adventurous and want to explore SQL Server 2012 RC0, you can download it from http://technet.microsoft.com/en-ca/evalcenter/hh225126.aspx. Give it a try!


Imagine for a moment that you are a SQL Server production DBA. You arrive at the office and there are two new co-worker requests in your e-mail inbox: Bob works in merchandise and needs read-only access to all the sales data so that he can run reports to forecast purchase volumes, Alice is a new junior DBA who only requires access to the meta-data. These seem like easy requests to fulfill – or are they?


The SQL Server Permission model can be used to solve this.


Securables are entities that SQL Server controls access to through permissions. Permissions enable a principal to perform actions on a securable. Across all securable scopes, the primary commands to control access to a securable are GRANT, DENY and REVOKE.



GRANT, DENY and REVOKE are T-SQL commands for managing permissions. Although, you might have used them to control permissions, couple of times REVOKE and DENY have confused most of us.


Here’s a brief explanation of GRANT, DENY AND REVOKE –

(a)     GRANT – Lets a principal perform an operation on a securable object.

(b)    DENY – Denies permission to perform an operation to a principal on a securable. Denies take precedence over all GRANT permissions and thus principals will not be allowed to perform the operation requested on the securable.

(c)     REVOKE – Removes the assigned GRANT/DENY permissions on a securable.


Example -

You can GRANT Bob EXECUTE permissions on a stored procedure but then realize you made a mistake. If you REVOKE the EXECUTE permission then you are simply reverting back to the state before the GRANT. Bob might still have access to the procedure through Windows group or SQL Server role memberships. If you DENY Bob EXECUTE permissions, then he will not be able to execute the stored procedure even if he receives the permission through another group or role membership.


Additionally, in the case of fixed role memberships, DENYs do not take precedence.


Securable scopes in SQL Server

SQL Server securables contain three scopes, which are used to assign permissions to users. The securables are nested and each securable contains various other securables as shown in the figure below –


(1)  Server scope which includes server roles, logins etc.

(2)  Database scope which includes database users, application roles, database roles, etc.

(3)  Schema scope which includes various database objects such as tables, views, stored procedures, etc.



Figure 1 : SQL Server Security Principals and Securables

To look at the permissions that are assigned at the server scope, use the sys.server_permissions catalog view. To look at the permissions that are assigned at the database scope, use the sys.database_permissions catalog view.



What permissions do I have?

You can use the sys.fn_get_my_permissions function to get a list of permissions held by the calling principal on a specified securable.

SELECT * FROM fn_my_permissions(NULL, 'SERVER');

SELECT * FROM fn_my_permissions (NULL, 'DATABASE');

This function only returns the permissions obtained by one of the following :

(1)    Permission directly granted to the principal and not denied.

(2)    Permission implied by a higher-level permission held by the principal and not denied.

(3)    Permission granted/held to a role or group of which the principal is a member of and not denied.


How many permissions and how do they relate to each other?

SQL Server Code-named ‘Denali’ has 214 permissions in total and we can’t cover every permission here. Look at the poster available here to learn about them and their relationship to each other.



Back to solving the permission problem for Bob and Alice

If we can identify the tables that Bob needs access to we can grant select permission to Bob to these tables. Alternatively, if there are many users such as Bob that might have similar data access requirements, we can create a database role (say MerchandiseReaders), assign the role required permissions and add users who need to access to read merchandise information into the MerchandiseReaders role.


Since Alice only requires access to the meta-data, it is sufficient to grant Alice VIEW ANY DEFINITION and VIEW SERVER STATE permissions.


In the next two posts, we will go into details on topics related to ownership chaining, module signing and suggesting some best practices around SQL Server permissions such as least privilege.


Comments (0)

Skip to main content