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?

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

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

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.

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.

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