SQL Server provides a number of settings for controlling access to data, metadata, and other SQL Server resources. For those who are new to SQL Server security or don’t manage security routinely, configuring these settings can seem a complex and confusing process. If you get the settings right, everybody is happy, but if you get them wrong, your users might not be able to access the data they need or, worse still, unauthorized users might be able to get at sensitive information or carry out malicious attacks.

To effectively protect SQL Server, you must be able to provide approved users with the access they need to specific SQL Server resources, without compromising those or other resources, a process that involves the use of three important types of components:

• Principals: Entities that can be authenticated to access the SQL Server resources. For example, your Windows login can be configured as a principal that allows you to connect to a SQL Server database. SQL Server supports three types of principals: logins, users, and roles. Logins exist at the server level, users exist at the database level, and roles can exist at either level.
• Securables: SQL Server resources that can be accessed by a principal. Securables are the actual resources you’re trying to protect, whether at the server level (e.g., availability groups), database level (e.g., full-text catalog), or the schema level (e.g., table or function).
• Permissions: Types of access granted on a securable to a specific principal. For example, you can grant a Windows login (the principal) the ability to view data (the permission) in a specific database schema (the securable).

Together, these three types of components can help you protect a SQL Server environment and its data at every level, from the availability groups down to the individual database objects. Generally, the process used for providing access to SQL Server resources is to create the principals you need and then configure them with the necessary permissions to access specific securables.

Server login principals

Logins provide a mechanism for users to connect to the SQL Server platform at the server level. A login serves as a user’s initial entry point into the system. Generally, your first step in providing access for your users is to set up the necessary logins so they can make the initial connections to the server. The database engine supports two types of logins: Windows and SQL Server.

A Windows login can be based on a Windows domain account—either an individual user account or a group account—or on a Windows local account. If based on a group account, any permissions granted to that login apply to all members of the group, which can be a handy approach to configuring security for multiple users who require the same level of access.

To create a login, you can use SQL Server Management Studio (SSMS) or use the CREATE LOGIN T-SQL statement. For example, the following statement creates a login based for a local Windows user account (WinUser1) on the srv01b Windows computer:

The FROM WINDOWS clause indicates that the login should be mapped to a Windows account. In this case, we’ve specified the computer name and account name for a local user, but we could have instead specified a domain user, following the same format, as in WinDomain1\WinUsr1. The CREATE LOGIN statement supports more options of course, depending on the type of user account being specified, but overall the statement is fairly straightforward. You’re merely creating an object that exists at the server level to provide access to the SQL Server environment.

If you’re SQL Server installation is configured to run in mixed mode—that is, it supports both Windows and SQL Server authentication methods—you can also create SQL Server logins, which are specific to a SQL Server instance and not tied to Windows accounts in any way. Although Windows logins are generally the recommended approach, you might need to support SQL Server logins in specific situations, such as providing access to users who are not tied to a Windows domain. The built-in sa account, the bane of many in IT, is a type of SQL Server login.

When users try to connect to SQL Server, the database engine validates their logins against the master database. This allows them to connect to the SQL Server instance, although they won’t have the permissions necessary to do much else. In fact, to be able to access database resources, the login must be tied to a specific database user principal, which is then granted the permissions necessary to access the database objects.

Database user principals

A login’s scope applies to the database engine as a whole, but it does not provide access to the individual database components. For the users associated with those logins to be able to access database resources, you must create users within the target databases and map those principals back to the server logins. You can create a database user with the same name as its associated login or with a different name. You can also map a login to users in multiple databases; however, a login can be mapped to only one user within a database at any given time.

As with server-level logins, you can use SSMS or T-SQL to create a database user, but you must do so within the context of the specific database. For example, we can create a database user named DbUser1 within the AdventureWorks2014 database, based on the srv01b\WinUser1 login

The FOR LOGIN clause provides the necessary mapping back to the server login. We could have created the user with the same name as the login, but I used a different name here to demonstrate how easy it is to choose whatever name you like, as long as it is unique within the database. This approach also allows us to simplify the qualified name being used for the login.

As you can see, creating a database user that maps back to a server login is a fairly straightforward process. The two principals work in conjunction with each other to enable a user to access the server and database, assuming the necessary permissions have also been configured for that user.

Note that you can also create database users that do not map back to a server login. In other words, you can create users without creating logins. These types of users are generally used for contained databases that can be easily moved between SQL Server instances. For this reason, a database’s containment feature must be enabled before you can create these types of users.

Server and database roles

After you create a login or user principal, you can configure permissions on either one to control access at the server or database level. Before you do that, however, consider that SQL Server also supports a third type of principal, referred to as a role. A role can exist at either the server or database level and can help manage user access to securables more efficiently.

You can think of a role as a type of container for holding one or more logins, users, or other roles, similar to how a Windows group can hold multiple individual and group accounts. This can make managing multiple principals easier when those principals require the same type of access to SQL Server. You can configure each role with permissions to specific resources, adding or removing logins and users from these roles as needed.

SQL Server supports three types of roles: server, database, and application. Server roles share the same scope as logins, which means they operate at the server level and pertain to the database engine as a whole. As a result, you can add only server-level principals to the roles, and you can configure the roles with permissions only to server-level securables, not database-level securables.

SQL Server provides a set of fixed server roles that are each preconfigured with a set of permissions that cannot be changed. The fixed roles allow the principals assigned to those roles to carry out specific tasks. For example, members of the sysadmin fixed server role can perform any actions on the server, but members of the serveradmin role can only change server-wide configuration options and shut down the server.

To view a list of fixed server roles, you can run the sp_helpsrvrole system stored procedure, as shown in the following example:

As of SQL Server 2012, you can also create user-defined server roles that let you configure the permissions however you need to. Creating a server role is as simple as creating any other type of principle. For instance, you can define a CREATE SERVER ROLE statement that specifies nothing more than the name of the role, as shown in the following example:

In this case, we’re creating a server role name SrvRole1. However, the role exists only as a server-level object on the SQL Server instance, without the capacity to do anything. From here, we must add the login principals we want to include in the role and eventually configure the role with the necessary permissions. (We can actually add the principals and configure the permissions in whatever order we want.)

To add a principal to the SrvRole1 server role, we can use the ALTER SERVER ROLE statement, as shown in the following example:

The statement includes the ADD MEMBER clause, which specifies the srv01b\WinUser1 server login. At this point, we could add other logins or user-defined roles, but we cannot add a fixed server role.

At times, you will likely want to be able to track which principals have been assigned to your roles. You can use SSMS or you can create a T-SQL query that retrieves data from the sys.server_principals and sys.server_role_members catalog views, as shown in the following example:

The sys.server_role_members catalog view merely matches the principal IDs of the server roles to their members. To get the information we need, we must join each column in the view to the principal_id column in the sys.server_principals catalog view. The statement also includes a WHERE clause to limit the results to the SrvRole1 server role. As expected, the statement returns only the value srv01b\WinUser1.

Creating a database role is just as simple as creating a server role. As before, we can use SSMS or issue a T-SQL statement, in this case, the CREATE ROLE statement. For instance, the following example creates the DbRole1 database role:

To add a principal to the DbRole1 database role, we can use the ALTER ROLE statement, as shown in the following example:

In this case, we’ve added the DbUser1 principal. Similar to server roles, we can add other database users or user-defined database roles, but not fixed database roles or any type of server principal.

SQL Server also include catalog views for retrieving information about database roles and principals. For example, the following SELECT statement uses the sys.database_principals and sys.database_role_members catalog views to retrieve the members of the DbRole1 database role:

This statement is much like the one we used to retrieve details about the SrvRole1 server role, only this case, its focus is on the DbRole1 database role and consequently returns the value DbUser1.

Securables and permissions

Once we have our principals in place, we can start assigning them the permissions they need to access the various securables. A securable is a specific SQL Server resource whose access is controlled by the database engine through the use of permissions.

SQL Server includes securables at three different scopes:

• Server-scoped securables include such resources as logins, server roles, availability groups, endpoints, and databases as a whole.
• Database-scoped securables include such resources as users, database roles, certificates, asymmetric keys, assemblies, full-text catalogs, and schemas as a whole.
• Schema-scoped securables include such resources as tables, views, types, functions, procedures, and XML schema collections.

For each securable, SQL Server supports a set of permissions that are specific to that securable’s scope and function. You can grant, deny, or revoke permissions on any securable to any principal, within the context of the specific principal and securable. For example, you can grant the SELECT permission on the Employee table to the DbUser1 database user, which allows the user to retrieve data from that table.

SQL Server supports three T-SQL statements for configuring permissions on a principal:

• GRANT: Grants permissions on a securable to a principal.
• DENY: Denies permissions on a securable to a principal. This can be important because it prevents the principal from inheriting other permissions that it has been granted.
• REVOKE: Removes previously granted or denied permissions on a securable to a principal.

Each of these statements supports a fairly complex syntax, coupled with the intricacies of the permission structure itself. A good place to start to more fully understand how permissions work is with the MSDN article Permissions (Database Engine). You might also want to review the SQL Server documentation about each of the permission-related statements:

• GRANT (Transact-SQL)
• DENY (Transact-SQL)
• REVOKE (Transact-SQL)

Let’s look at a few examples that demonstrate principals, securables, and permissions in action. We’ll start by running an EXECUTE AS statement to change the execution context of our session to the DbUser1 database user account in the AdventureWorks2014 database:

Now let’s try running the following T-SQL statements as DbUser1:

In each case, we will receive an error indicating that the user does not have the permissions necessary to access the requested resources. The reason is, of course, that the user has not been granted these permissions. Before we can do that, however, we must run a REVERT statement to return to the context of the user that does have the necessary permissions (the user you likely logged in as originally):

Now that we’re back to our old selves, let’s use a GRANT statement to grant the EXECUTE permission on the uspUpdateEmployeeHireInfo stored procedure to the DbRole1 database role, which includes the DbUser1 database user:

This example shows the GRANT statement in one of its simplest forms. However, as pointed out earlier, the statement supports a complex syntax that provides numerous options, depending on the securables and principals.

After granting the necessary permissions, we can rerun the previous EXECUTE AS statement to change the context back to DbUser1 and then rerun the SELECT and EXECUTE statements. This time, we’ll still receive errors when we try to run the SELECT statements, but the EXECUTE statement will run fine.

What’s interesting about this is that the stored procedure updates the Employee table and inserts a role in the EmployeePayHistory table, even though DbUser1 has not been granted this type of access to these tables. This works because the owner of the stored procedure, dbo, has access to the underlying tables, a process referred to as ownership chaining. You can find a great discussion about this topic in Phil Factor’s Simple-Talk article Schema-Based Access Control for SQL Server Databases.

Now let’s revert back to our original user and run the following GRANT and DENY statements:

This time around, we’re granting the SELECT permission on the HumanResources schema to the DbRole1 database role, but denying the SELECT permission on the JobCandidate table, which is part of that schema. If we do not explicitly issue a DENY statement, the user would be able to access the table because it is part of the HumanResources schema.

If we again change the session context back to DbUser1 and then rerun the SELECT and EXECUTE statements, we will now be able to run the stored procedure and retrieve data from the Employee and EmployeePayHistory tables, but we will not be able to retrieve data from the JobCandidate or Person table because the DbUser1 user has been explicitly denied SELECT permission on the JobCandidate table and the Person table is in the Person schema, not HumanResources, so no access has been granted.

At some point, you’ll likely want to be able to view the permissions you have configured on a specific securable (after reverting back to an authorized user). One way you can do this is to run a SELECT statement that joins the sys.database_principals, sys.database_permissions, and sys.schemas catalog views, as shown in the following example:

We have to include the sys.schema catalog view in the mix because the major_id value in the sys.database_permissions catalog view does not map to the actual schema name, as do other object types, so I chose to join the sys.schema catalog view in this way to give me what I need. The statement returns the following results, which show the permissions we granted to the DbRole1 principal.

At any point in the process, we can revoke the permissions that have been granted on a securable to a principal. For example, the following REVOKE statements remove all the permissions we configured on the DbRole1 database role:

Once you run these statements, the principals that have been added to the DbRole1 role will no longer have access to the specified securables, unless they’ve been granted access in another way.

Principals, securables, and permissions

There is much more to controlling SQL Server access than what we’ve covered here, and you should view this information only as a starting point. The T-SQL statements can be far more intricate, and the securable/permission mappings much more extensive.

Orchestrating access to SQL Server resources takes time and careful consideration. The more complex the system, the more work involved. That’s why using Windows groups along with server and database roles can be so helpful in mitigating some of the challenges of an involved implementation. They can help you grant access to SQL Server resources with just a few steps, making it relatively easy to make various server and database resources available to your users.

Of course, the flip side to this is that it can be just as easy to open up resources to unauthorized users. How often have administrators, when faced with approaching deadlines and failed connections, opened up the database beyond what might have been necessary or advisable? Whenever you’re setting up access in SQL Server, you should always be thinking in terms of the principle of least privilege, no matter what the circumstances. And you should always be vigilant against inadvertently granting access to those who shouldn’t have it.