SQL SERVER – Introduction to SQL Server Security – A Primer

Let’s get some basic definitions down first about SQL Server Security. Take the workplace example where “Tom” needs “Read” access to the “Financial Folder”. What are the Securable, Principal, and Permissions from that last sentence?

SQL SERVER - Introduction to SQL Server Security - A Primer book1

  • A Securable is a resource that someone might want to access (like the Financial Folder).
  • A Principal is anything that might want to gain access to the securable (like Tom).
  • A Permission is the level of access a principal has to a securable (like Read).

Let’s re-word the story above using the right terms. The Principal, named Tom, needs the Permission of Read, to the Securable item Financial Folder.

In this lesson, you will learn how to use three powerful keywords (GRANT, DENY and REVOKE) of the Data Control Language (DCL). All of the examples shown below will presume the following:

  • The Securable is the entire SQL Server itself.
  • The Principal is named either Murray or Sara.
  • The Permission(s) will either be ‘Control Server’ or ‘Alter Any Database’.

Granting Permissions

The GRANT keyword is a DCL statement that creates permissions on a securable and grants these permissions to a principal. OK, that sounds a lot like ‘Geek Speak’, so let’s use a simple example to demonstrate how this works.

With the GRANT keyword, you can tell the SQL Server (securable) to allow the ‘Alter Any Database’ (permission level) to Murray (principal), while also allowing the ‘Control Server’ (permission level) to Murray and Sara (principal).

GRANT ALTER ANY DATABASE TO Murray
GRANT CONTROL SERVER TO Murray
GRANT CONTROL SERVER TO Sara
GO

In the code above, the GRANT(s) allow Murray two explicit permissions and Sara a single explicit permission to the SQL Server. Despite this, they have the same level of resource access to the SQL Server securable. That’s because ‘Control Server’ can do everything including ‘Alter Any Database’. In this scenario, Sara can effectively do everything at the same level as Murray.

Principal

Securable

Permissions

Murray

SQL Server

Control Server = GrantedAlter Any Database = Granted

Sara

Solarwinds

SQL Server

Control Server = Granted

Denying Permissions

Currently, Murray and Sara both have full control of the SQL Server securable. Even though Murray has more explicit permissions listed, they both have the same effective permissions.

With the DENY keyword, you can tell the SQL Server to explicitly deny the ‘Alter Any Database’ to Sara, without affecting any of the permissions already given to Murray. However; this action will prevent Sara from having the ability to modify or create any databases on the SQL Server securable.

You can keep the ‘Control Server’ permission in place, while denying Sara the ‘Alter Any Database’ permission, by simply writing the following DCL statement:

DENY ALTER ANY DATABASE TO Sara
GO

Sara has now been granted control to the SQL Server, but explicitly denied the permission to ‘Alter Any Database’. As you can see in the screenshot below, this restriction will cause a problem when Sara tries to create a database.

 SQL SERVER - Introduction to SQL Server Security - A Primer j2p1-2012

To summarize what has been done to the principals, Murray and Sara, review the Table below. (The change has been highlighted in red to make it easy to find).

Principal

Securable

Permissions

Murray

SQL Server

Control Server = GrantedAlter Any Database = Granted

Sara

SQL Server

Control Server = GrantedAlter Any Database = Denied

Revoking Permissions

Sara currently has limited control of the SQL Server, since there is a DENY statement on the ‘Alter Any Database’ permission for her. Murray effectively has full control of the SQL Server, as no permissions have been denied to him. In fact, the GRANT statement for him to ‘Alter Any Database’ appears redundant. Indeed, Murray would be unaffected if his ‘Alter Any Database’ permission no longer existed.  Let’s see what it takes to make this change.

Your goal is to leave the ‘Alter Any Database’ permission for Murray as unspecified or revoked in the SQL Server’s access list. Neither the GRANT or DENY keywords are able to accomplish this task.

With the REVOKE keyword, you can instruct the SQL Server securable to revoke the ‘Alter Any Database’ permission for Murray, without affecting his current ‘Control Server’ permission. You can now achieve your goal by writing the following DCL statement:

REVOKE ALTER ANY DATABASE TO Sara
GO

The REVOKE keyword will simply remove an existing GRANT or DENY permission from the SQL Server access list. Review the results of the previous code in the table below.

Principal

Securable

Permissions

Murray

SQL Server

Control Server = Granted

Sara

SQL Server

Control Server = GrantedAlter Any Database = Denied

Revoke sounds like a penalty or a roadblock to someone’s permissions. This indeed can be the case, as REVOKE removes both GRANT and DENY permissions. In reality, you can use REVOKE to easily restore Sara’s permissions to the same level as Murray (full control) by writing the following DCL statement:

REVOKE ALTER ANY DATABASE TO Sara
GO

What is the end result?  Sara’s restriction to ‘Alter Any Database’ has been removed. The Murray and Sara principals now share ‘Control Server’ permission on the SQL Server securable. You can review the final results of this coding exercise in the table below.

Principal

Securable

Permissions

Murray

SQL Server

Control Server = Granted

Sara

SQL Server

Control Server = Granted

Reference: Pinal Dave (https://blog.sqlauthority.com), Amazon

Solarwinds
, , ,
Previous Post
SQLAuthority News – Fast Track Data Warehouse Reference Guide for SQL Server 2012
Next Post
SQL SERVER – Introduction to Hierarchical Query using a Recursive CTE – A Primer

Related Posts

7 Comments. Leave new

  • Shouldn’t the first revoke example use Murray instead of Sara.
    Nice primer.

    Reply
    • Qaiser Mehmood
      January 28, 2013 6:34 pm

      Yes. You are right. It should be

      REVOKE ALTER ANY DATABASE TO Murray
      GO

      Instead of
      REVOKE ALTER ANY DATABASE TO sara

      Reply
  • Shantanu Gupta
    April 23, 2012 12:08 pm

    This was a good article, I was waiting for you to write articles on security topic.
    I still have lot of things open in my mind on how to create a user, role so that I can assign same level of permission to multiple users by just adding him to a specific role. I tried using Windows principal but could’nt figure out due to less knowledge on windows user group side. Can you write some more articles covering such topics and Execute AS CALLER|SELF|Owner depicting the difference.

    Reply
  • nice explanation. I think statement before last should like
    REVOKE ALTER ANY DATABASE TO Murray
    GO
    Instead of REVOKE ALTER ANY DATABASE TO sara

    Reply
  • This is one of your best and clearest articles to date. Very helpful. Thanks PD.

    Reply
  • Very nice explanation. Thanks.

    Reply

Leave a Reply

Menu