SQL SERVER – Introduction to SQL Server Security – A Primer

This blog post is inspired from Beginning SQL Joes 2 Pros: The SQL Hands-On Guide for Beginners – SQL Exam Prep Series 70-433 – Volume 1.

[Amazon] | [Flipkart] | [Kindle] | [IndiaPlaza]


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

  • 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

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.

 

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

This blog post is inspired from Beginning SQL Joes 2 Pros: The SQL Hands-On Guide for Beginners – SQL Exam Prep Series 70-433 – Volume 1.

[Amazon] | [Flipkart] | [Kindle] | [IndiaPlaza]

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

12 thoughts on “SQL SERVER – Introduction to SQL Server Security – A Primer

  1. 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.

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

  3. Pingback: SQL SERVER – Quiz and Video – Introduction to SQL Server Security « SQL Server Journey with SQL Authority

  4. Pingback: SQL SERVER – SQL in Sixty Seconds – 5 Videos from Joes 2 Pros Series – SQL Exam Prep Series 70-433 « SQL Server Journey with SQL Authority

  5. Pingback: SQL SERVER – 5 Videos from Joes 2 Pros Series Exam Prep Series 70-433 – SQL in Sixty Seconds « SQL Server Journey with SQL Authority

  6. Pingback: SQL SERVER – Weekly Series – Memory Lane – #026 | SQL Server Journey with SQL Authority

  7. Pingback: SQL SERVER – Difference Between Login and User – SQL in Sixty Seconds #070 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s