Using SQL Server Permissions

SQL Server 2005 was the first major version of SQL Server released after Bill Gates' infamous Trustworthy Computing memo in 2002. That memo changed the company's approach and attitude toward the security of its products, and the security features in SQL Server 2005 received a major overhaul. As a result, SQL Server 2012 and 2014 are incredibly secure products out-of-the-box, building on those overhauled features and making them stronger with each new release. But keeping them secure as you add databases and give users access can be a challenge!

One of the most striking changes in 2005 was the new permissions infrastructure component of server authorization. Finally gone were the days of having to assign users to system roles to grant them a single permission, along with other serious security shortcomings. Now, the permissions system is highly granular, letting you assign the minimum permissions necessary for a principal to do the job they need to do, which is the idea behind the security principle of Least Privilege.

Related: Key Ways to Secure ASP.NET applications with a SQL Server Back End

When using permissions, there are three major things to consider:

The basic idea of permissions is that you grant a certain type of a permission on some object or objects in the instance of SQL Server to some principal.

Like anything in SQL Server, you need to plan and design your permissions to provide the maximum protection for your data and server, while balancing the need for people and processes to access the data to do their jobs. This really is a balancing act, but is critical to implementing Least Privilege in SQL Server.

Principals: Who Gets the Permission?

I won't spend much time on principals in this article, but I do want to point out that SQL Server has a variety of types of principals that can be granted a permission on an object. The list of available principals, shown below, ranges from real users authenticated either by Windows or SQL Server, various mappings of principals at different scopes, as well as applications, asymmetric keys, and even security certificates.

Windows-level principals

SQL Server-level principals

Database-level principals

Like Windows, a principal can be a single user or a collection of users, called a group in Windows and a role in SQL Server. You'll generally want to assign users to groups in Windows (which is then mapped to a role in SQL Server) or roles within SQL Server, then assign permissions to a role. That makes management of permissions far easier, particularly when you also deal with groups and hierarchies of objects, which I discuss later in this article. Keep things as simple as possible, lest you unintentionally miss security holes in the complexity!

The Object of the Permission

Just about every user-definable object in SQL Server is a securable object, something that you can control access to using permissions granted to a principal. There are three scopes of securable objects—server, database, and schema—that form a hierarchy of securable objects in an instance of SQL Server, as shown in Figure 1. The figure shows the most common objects you'll need to secure in each scope (but not all of them). It also shows how two of the objects—database and schema—are containers for other objects.

Figure 1: The hierarchy of securable objects in an instance of SQL Server

It's important to understand this hierarchy because you can secure every object shown in the figure separately with permissions. That makes sense for individual objects like server roles, certificates, and tables. But it has enormous significance for the container objects, database and schema. Each of those objects contain other objects. The cool security implication is that you can set permissions on the container that then apply to all the contained objects. For example, you could grant the SELECT permission on a schema, and the principal would receive that permission on all the tables in the schema. So just like roles aggregate permissions on potentially many principals, the container objects aggregate permissions on objects.

You can also get more granular and grant SELECT on a schema but deny that permission for just one of its tables, which still makes the whole scheme far easier to manage than if you had to assign each permission for each principal on each object individually.

Permission Statements

Now that you know something about principals and securable objects, it's time to talk about permissions themselves and how you can grant them. You can perform these actions using the pretty tools in SQL Server Management Studio (SSMS) or by writing T-SQL statements. I'll focus on the latter here, mainly because that's how you can make use of the full range of permissions features.

There are three permissions statements that you can use. A GRANT statement grants a permission on a securable object to a principal. A REVOKE statement undoes what the GRANT statement does: it removes a permission on an object previously granted to a principal. If the principal doesn't have the permission when the REVOKE statement executes, the principal still doesn't have the permission, and no error occurs. It's important to understand that a revoked permission can still be inherited through membership in a role that has the permission. This can cause some rather convoluted situations, so a best practice is to rarely grant permissions to individual users, logins, or other single principals, only to roles.

The third permission statement is DENY. A DENY statement also revokes a permission, but does it in such a way that the principal cannot inherit the permission through role membership. When you deny a permission, that principal doesn't have it, period. Denying permissions can let you get very granular with permissions. A common example is to grant everyone in a department a set of permissions through their membership in a role. But then you can fine tune the scheme by denying permissions either to certain users or to another role that only a subset of users belong to, taking advantage of the ability of users to belong to multiple roles.

One word of caution: It can be very easy to create extremely complex and convoluted permission sets using these three statements, which can make everything difficult to understand and maintain. This is a situation ripe with possibilities for inadvertently creating security holes and vulnerabilities. Keep it simple, and you're way more likely to keep it secure.

Using Permissions at the Server Instance Level

Let's put this knowledge of permissions to work and look at some T-SQL code to perform various kinds of actions. All of the code I show here is included in the Permissions Code.sql file that accompanies this article via the "Download the Code" link at the top of the page, along with some cleanup and support code, as well as some explanatory comments. The code explores permissions at both the server and database levels.

The code starts by creating a server login Bonsai (with a reasonably strong password!) and a user Bonsai mapped to the login in the AdventureWorks2012 database. Use whatever database you like, but some of the code uses objects and data in that database.

CREATE LOGIN Bonsai WITH PASSWORD = 'EDxQk!R209*:ZJ5';GOUSE AdventureWorks2012;GOCREATE USER Bonsai FOR LOGIN Bonsai WITH DEFAULT_SCHEMA = Production;GO

The code then creates a user-defined server role, LimitedAdmin, which will get most, but not all, sysadmin privileges on the instance of SQL Server. The role first gets the CONTROL SERVER permission, which essentially makes it a sysadmin role. Then the code denies a few permissions, including the ability to create or alter other server roles and logins. Probably the biggest hit to its power is the denial of permission to alter any database, which includes or covers the ability to create new databases. So this role really will only have permissions at the server instance level.

USE master;GOCREATE SERVER ROLE LimitedAdmin;GOGRANT CONTROL SERVER TO LimitedAdmin;GODENY ALTER ANY SERVER ROLE TO LimitedAdmin;DENY ALTER ANY LOGIN TO LimitedAdmin;DENY ALTER ANY DATABASE TO LimitedAdmin;

Now it's time to test the powers of the LimitedAdmin role. For that, we'll use the code in Listing 1, which attempts to perform four actions: create a server role, create a login, create a database, and turn on SQLCLR code in the instance. We've denied permissions on the first three actions, but the fourth should work because sysadmins have the ability to alter system settings and we've not denied that permission on the role.

CREATE SERVER ROLE TempRole;CREATE LOGIN TempLogin WITH PASSWORD = 'AK8l*9%fwy/xvH';CREATE DATABASE TempDatabase;EXEC SP_CONFIGURE 'show advanced options' , '1';GORECONFIGURE;GOEXEC SP_CONFIGURE 'clr enabled', '1'GORECONFIGURE;GO

To test the abilities of LimitedAdmin, we'll use the Bonsai login created earlier, changing the execution context as shown below, and then executing the statements in Listing 1. Since we've not yet added Bonsai to the LimitedAdmin role, he is still just a regular login without very many permissions, and all the statements fail, as you can see in Figure 2. This provides a kind of control test to make sure that a base user does not have any server-level permissions.

EXECUTE AS LOGIN = 'Bonsai';

Figure 2: Testing the permissions of Bonsai before he is a member of the LimitedAdmin role

Then we'll revert to sysadmin permissions using the following statement.

REVERT;

Now we'll repeat the process, but first add Bonsai to the LimitedAdmin group using the following statement, so that Bonsai has the permissions of the role in addition to any other permissions he already had. (In this example, he had none.)

ALTER SERVER ROLE LimitedAdmin ADD MEMBER Bonsai;

Now, when we change the execution context to Bonsai, the first three operations still fail, but enabling SQLCLR code works fine. Even though we granted the LimitedAdmin role CONTROL SERVER permission, which covers just about everything you can do in a server instance, we also denied some permissions.

If you decide that you don't want the LimitedAdmin role to be able to change server settings such as enabling SQLCLR code, you can deny the SETTINGS permission with the following statement.

DENY ALTER SETTINGS TO LimitedAdmin;

Now, executing the statements in Listing 1 will be completely unsuccessful, because LimitedAdmin is a very limited sysadmin indeed! But this example—admittedly contrived—shows what you can do with granular permissions at the server level. And keep in mind that you don't have to start by granting the CONTROL SERVER permission. You can start smaller, and grant the role only the permissions that members need to perform their jobs, rather than starting big and denying individual permissions.

Using Permissions at the Database Level

You can take advantage of SQL Server's granular permissions at the database level as well. In this part of the article, we'll create a user-defined database role and explore another aspect of permissions: you can control who can perform various types of actions on certain database objects.

In this case, the code starts by moving to the AdventureWorks2012 database and then creates a ProdDataEntry user-defined role, adding Bonsai to the role.

USE AdventureWorks2012;GOCREATE ROLE ProdDataEntry AUTHORIZATION dbo;ALTER ROLE ProdDataEntry ADD MEMBER Bonsai;GO

Next, we need to grant some permissions to the role, and we'll revoke one as well. The idea here is that the members of the role need to be able to insert and update records in a couple of tables in the Production schema. This is where controlling the permissions on actions come into play. The following statements grant INSERT and UPDATE permissions on the Production.UnitMeasure and Production.ProductCategory tables, as well as the SELECT statement on the ProductCategory table. I'll use that to show how you can control actions on your data at a very granular level.

GRANT INSERT ON Production.UnitMeasure TO ProdDataEntry;GRANT UPDATE ON Production.UnitMeasure TO ProdDataEntry;GRANT INSERT ON Production.ProductCategory TO ProdDataEntry;GRANT UPDATE ON Production.ProductCategory TO ProdDataEntry;GRANT SELECT ON Production.ProductCategory TO ProdDataEntry;

The code then grants EXECUTE permission on the dbo.uspGetEmployeeManagers stored procedure, but revokes that permission from the dbo.uspGetManagerEmployees procedure. This shows how different object have different kinds of permissions. Keep in mind that if Bonsai has EXECUTE permission on uspGetManagerEmployees through membership in another role, he'll be able to run the procedure. This is how REVOKE is different from DENY.

GRANT EXECUTE ON dbo.uspGetEmployeeManagers TO ProdDataEntry;REVOKE EXECUTE ON dbo.uspGetManagerEmployees TO ProdDataEntry;

Then, we can use the code in Listing 2 to test permissions when executing the code as Bonsai. Read through the code before reading on to see if you can figure out whether each statement succeeds or fails.

EXECUTE AS USER = 'Bonsai';-- Snippet 1INSERT INTO Production.UnitMeasure (UnitMeasureCode, Name)     VALUES ('BAR', 'Standard Bar');-- Snippet 2SELECT * FROM Production.UnitMeasure WHERE UnitMeasureCode = 'BAR';-- Snipped 3INSERT INTO Production.ProductCategory (Name)     VALUES ('Navigation');-- Snippet 4SELECT * FROM Production.ProductCategory WHERE Name = 'Navigation';-- Snippet 5INSERT INTO HumanResources.Department       (Name, GroupName)VALUES       ('Advertising', 'Sales and Marketing');GO-- Snippet 6DECLARE @rc INT;EXECUTE @rc = dbo.uspGetEmployeeManagers 113;GO-- Snippet 7DECLARE @rc INT;EXECUTE @rc = dbo.uspGetManagerEmployees 113;GOREVERT;

Snippet 1 succeeds because Bonsai has the INSERT permission on the UnitMeasure table, but Snippet 2 fails because he doesn't have the SELECT permission. Which is a bit strange, but nothing stops you from doing strange things with permissions! Snippets 3 and 4 both succeed, because the role has both INSERT and SELECT permissions on the ProductCategory table. Snippet 5 fails, because the role has no permissions at all on the HumanResources.Department table. Snippet 6 succeeds because the role has the EXECUTE permission on the uspGetEmployeeManagers stored procedure, but snippet 7 fails because the permission on the uspGetManagerEmployees procedure was revoked earlier.

SQL Server makes available a fair amount of metadata about permissions, and Listing 3 shows a few ways that you can get access to the metadata. The first statement lists the permissions that the ProdDataEntry role has in the AdventureWorks2012 database after running the earlier code. The other three statements show how you can use sys.fn_builtin_permissions to get a list of all built-in permissions in the server instance, all at the server level, and all at the database level. This is a great way to explore the full range of permissions that are available. You'll find that there are a lot!

USE AdventureWorks2012;GOSELECT DB_NAME() AS 'Database', p.name, p.type_desc, dbp.state_desc,       dbp.permission_name, so.name, so.type_descFROM sys.database_permissions dbp       LEFT JOIN sys.objects so ON dbp.major_id = so.object_id       LEFT JOIN sys.database_principals p ON dbp.grantee_principal_id = p.principal_idWHERE p.name = 'ProdDataEntry'ORDER BY so.name, dbp.permission_name;SELECT * FROM sys.fn_builtin_permissions(DEFAULT);SELECT * FROM sys.fn_builtin_permissions('SERVER') ORDER BY permission_name;SELECT * FROM sys.fn_builtin_permissions('DATABASE') ORDER BY permission_name;

Go Forth and Use Permissions

Security got a major overhaul back in SQL Server 2005, and each new version has just made things better. Permissions are far more granular than they were in earlier versions of SQL Server, and you have a lot of control over who gets to do what in your server instance. With all of the options for principals, securable objects, and permissions, it can be a bit overwhelming to figure out how best to grant, revoke, and deny permissions. But it's worth the effort for a far more secure database server, and it is never a good idea to grant users or roles admin-level permissions just to keep things easy!

Related: SQL Server 2012 Keeps Your Data a Little More Secure