In general, you implement user and object security in SQL Server by assigning permissions on objects to principals. But what is a SQL Server principal? And what does it get a permission on? In this stairway level, you’ll learn about the various principals that can be authorized through permissions to perform actions and access securable objects in the SQL Server instance. An important set of principals in SQL Server are roles, and you’ll learn about how roles can make managing security far easier than it would be working with individual users as the only type of principal. You’ll also learn about the securable objects in SQL Server, setting the stage to learn about permissions in the next Level.

Authorization

Authentication, covered in Level 2, is only part of accessing all of the goodies in a database server. Authentication is a little like having a passport that proves who you are but has no visa—you need a visa to enter and move around the country. In this level you’ll learn about authorization and how it acts as the visa that provides access to database objects.

A principal is a user or process that can access one or more of the securable objects in a SQL Server or its databases. A securable object (or just securable) is a protected resource, something that only certain people or processes can view or change, such as the data in a table. A permission enables a principal to gain a specific type of access to a securable.

To continue the passport analogy, the principal is the holder of the passport, the person whose picture is therein. The securable is the country that the principal wants to visit, and the permission is the visa to cross the country’s borders and enjoy a visit.

Principals

A principal, in the context of security is any user (the human kind), group of users (called a role in SQL Server), or code running in a process that can request access to a securable object and be granted or denied access to it. All Windows and SQL Server logins are principals, as well as the users they are mapped to within databases. The following list shows the hierarchy of most of the more important principals in SQL Server, ranging from server-wide principals with permissions spanning the SQL Server instance, to database-level principals:

Windows-level principals

• Windows domain login
• Windows group
• Windows local login

SQL Server-level principals

• SQL Server login
• SQL Server login mapped to a certificate
• SQL Server login mapped to a Windows login
• SQL Server login mapped to an asymmetric key

Database-level principals

• Application role
• Database role
• Database user
• Database user mapped to a certificate
• Database user mapped to a Windows login
• Database user mapped to an asymmetric key
• Public role

It is important to understand this hierarchy, because the scope of a principal partially determines the scope of a permission granted to it. For example, a database user can have permissions granted to it only within the context of that database. A SQL Server-level principal can have permissions throughout the server, and Windows-level principals can have permissions that extend beyond the confines of SQL Server, into the local instance of Windows and across the network.

Notice in the previous list that a principal can, among other things, be a login (or user) as well as a role. Roles in SQL Server are analogous to Windows groups. Users who have membership in a role inherit the permissions assigned to the role. Roles make security administration much easier because you don’t need to manage complex sets of permissions for individual users. SQL Server supports the following kinds of roles:

• Fixed server roles: SQL Server built-in roles for performing server-level tasks.
• User-defined server roles: Custom server roles that you create, assign server-level permissions to, and assign logins to so that they inherit permissions on server objects.
• Fixed database roles: Built-in roles for performing database tasks and for assigning basic permissions.
• User-defined database roles: Custom database roles that you create, assign permissions to, and then add users to so that the users inherit permissions on database objects.

You can assign users to multiple roles. Roles can also be nested, but don’t get too carried away—you’ll suffer a performance penalty if your nesting scheme is too complex, and it can make maintenance and troubleshooting a nightmare.

Fixed Server Roles

Fixed server roles are built-in roles in SQL Server and you cannot alter them in any way—you can only add logins to them. They exist at the server level only for performing administrative tasks. The fixed server roles in SQL Server are listed here with the actual role names in parentheses:

• System Administrator (sysadmin): Perform any activity in the SQL Server instance. This role encompasses all the other roles—once a user is a member of sysadmin, they don’t need any other role. Members of sysadmin can do anything they please, so it’s a good idea to restrict membership to only those who need it and can be trusted to have unlimited access.
• Bulk Insert Administrator (bulkadmin): Execute BULK INSERT statements to get data into a database rapidly.
• Database Creator (dbcreator): Create and alter databases.
• Disk Administrator (diskadmin): Manage the various disk files that store databases.
• Process Administrator (processadmin): Manage the processes that are running in SQL Server.
• Server Administrator (serveradmin): Configure server-wide settings. Despite the similarity of the name with System Administrator, serveradmin is a very different and much more limited role.
• Setup Administrator (setupadmin): Install replication and manage extended procedures.
• Security Administrator (securityadmin): Manage the logins for the server.

Fixed server roles provide flexibility and security by allowing you to divide server tasks into parts. In other words, you don’t have to make someone a system administrator if they only need to create databases. Instead, make them a member of dbcreator, and they have all the permissions they need.

You can assign a login to a fixed server role either by using Management Studio or with Transact-SQL. To use Management Studio, perform the following steps:

1. Expand the Security section of the Object Explorer in Management Studio to show the list of Logins.
2. Right-click the Topaz login and select Properties from the pop-up menu.
3. In the Login Properties dialog box, select the Server Roles page. This lists all of the available server roles with a check box for adding the login to each. Notice that Topaz, like all logins, is already a member of the public role.
4. Assign the login to the dbcreator and diskadmin roles. Figure 3.1 shows the dialog box for the login Topaz.

Figure 3.1. Assigning the login Topaz to the dbcreator and diskadmin fixed server roles.

5. Click OK to save the changes.

Alternatively, you can add logins to a role by using the Server Roles node under the Security node in Object Explorer. To add Topaz to the securityadmin server role:

1. Expand the Server Roles node under the Security node in Object Explorer.
2. Right-click the securityadmin server role in Object Explorer and select Properties. This opens the Server Role Properties dialog box.
3. Click the Add button near the bottom right of the dialog box, which opens the Select Logins dialog box. You can either type Topaz and click Check Names, or click the Browse button to get a list of logins. Once you enter Topaz, the dialog box looks like Figure 3.2.

Figure 3.2. Selecting Topaz to add to a server role.

4. Click OK to add Topaz to the server role. The Server Role Properties dialog box looks like Figure 3.3.

Figure 3.3. Adding Topaz to the securityadmin server role.

5. Click OK to save the change.

The other way to add a login to a server role is with Transact-SQL, by using the sp_addsrvrolemember system stored procedure. The following code adds the existing login Topaz to the sysadmin role:

EXEC sp_addsrvrolemember 'Topaz', 'sysadmin';

Listing 3.1: Code to add a login to a server role.

You can find information about fixed server roles by running the two stored procedures, sp_helpsrvrole and sp_helpsrvrolemember. If you pass a valid name of a server role to sp_helpsrvrole, it will display the description of that role; otherwise it displays all server roles. Figure 3.4 shows both system stored procedures executing in Management Studio to display the description of the securityadmin role and its current membership.

Figure 3.4. Using system stored procedures to get information about server roles.

User-Defined Server Roles

A long awaited security feature in SQL Server 2012 was user-defined server roles. SQL Server has long had flexible user-defined database roles for database-level permissions (which you’ll learn about later in this level), but with custom server roles you can finally get as granular with server-level permissions.

In old versions of SQL Server, just about the only way to grant some kinds of permissions to users was to assign them to a built-in fixed server role, which usually had too many permissions. Making everyone a sysadmin was a horrible but common practice, particularly problematic because you can’t deny a sysadmin anything. This violates the principal of least privilege in a big way, but was often a practical necessity. SQL Server 2005 and later made all this more granular, letting you assign just about any specific server-level permission to a user, but lacked the ability to group those permissions into a server role.

SQL Server 2012 solved that problem with its support for user-defined server roles. Creating a new server role is as simple as using the CREATE SERVER ROLE statement:

CREATE SERVER ROLE LimitedDBA;

Listing 3.2: Code to create a new server role.

Then you can grant and deny the role any server-level permissions you want. The following code grants the CONTROL SERVER permission to the new role—akin to granting sysadmin privileges—then denies a few permissions to narrow down the privileges of the members of the server role. This is a very flexible way to grant the users who are members of the group specific permissions.

USE master;
GO
-- Grant the role virtual sysadmin permissions
GRANT CONTROL SERVER TO LimitedDBA;
-- And take some permissions away
DENY ALTER ANY LOGIN TO LimitedDBA;
DENY ALTER ANY SERVER AUDIT TO LimitedDBA;
DENY ALTER ANY SERVER ROLE TO LimitedDBA;
DENY CREATE SERVER ROLE TO LimitedDBA;        -- Covered by ALTER ANY SERVER ROLE
DENY UNSAFE ASSEMBLY TO LimitedDBA;

Listing 3.3: Code to add and deny permissions to a server role.

To test the role, the code in Listing 3-4 creates a login associated with a Windows group, DBAs, on a machine named Marathon, and adds the new login to the LimitedDBA role.

-- Create a login for DBAs Windows group
CREATE LOGIN [Marathon\DBAs] FROM WINDOWS;
-- Add to the server role
ALTER SERVER ROLE LimitedDBA ADD MEMBER [Marathon\DBAs];

Listing 3.4: Code to create a login and add it to a server role.

The code in Listing 3.5 then creates a SQL Server login carol, with no permissions whatsoever within the instance of SQL Server. Then the code attempts various actions under carol’s security context that require server-level permissions: create another login, view system information, and create another server role. All of these actions fail, as you can see in Figure 3.5, because the carol principal has no permissions to perform these actions.

-- Create carol login
CREATE LOGIN carol WITH PASSWORD = 'crolPWD123%%%';
EXECUTE AS LOGIN = 'carol';
-- Verify user context
PRINT suser_sname();
-- Can Carol alter logins?
CREATE LOGIN donkiely WITH PASSWORD = 'G@Sm3aIKU3HA#fW^MNyA';    -- No
-- Other server-level permissions?
SELECT * FROM sys.dm_exec_cached_plans;    -- No, requires VIEW USER STATE
CREATE SERVER ROLE CarolRole;                    -- No
REVERT;

Listing 3.5: Code to create a login and test to see if it has specific permissions.

Figure 3.5. Failed actions because carol has no permissions.

Next the code adds carol to the new LimitedDBA user-defined server role, and once again attempts to perform the same actions. As you can see in Figure 3.6, this time carol is able to get system information (the SELECT action), because that permission is granted through the CONTROL SERVER permission. But carol still can’t create logins or server roles, because those permissions were explicitly denied from the LimitedDBA role.

ALTER SERVER ROLE LimitedDBA ADD MEMBER carol;
-- Now does Carol have permissions?
EXECUTE AS LOGIN = 'carol';
CREATE LOGIN donkiely WITH PASSWORD = 'G@Sm3aIKU3HA#fW^MNyA';    -- Still not possible
SELECT * FROM sys.dm_exec_cached_plans;                            -- Yes, CONTROL SERVER covers VIEW USER STATE
CREATE SERVER ROLE CarolRole;                    -- Not possible
REVERT;

Listing 3.6: Code to again test whether a member of a server role has specific permissions.

Figure 3.6. Results of server-level actions with partial permissions through LimitedDBA.

In order to view all of the available server-level permissions that you can grant and deny to server roles, execute the following code. Figure 3.7 shows the results.

SELECT * FROM sys.fn_builtin_permissions('SERVER') 
    ORDER BY permission_name;

Listing 3.7: Code to view all of the available server-level permissions.

Figure 3.7. Partial list of server-level permissions.

You can create user-defined server roles to grant users and groups a very specific set of permissions that they need to do their job, and no more. This is far more flexible than earlier versions of SQL Server, making security management far easier with SQL Server 2012, and easier management inevitably means a more secure server.

Fixed Database Roles

Fixed database roles exist at the database level, not the server level, and control authorization only within that database. Each database has its own collection of fixed database roles, so you can configure the roles in each of your databases individually. Fixed database roles are like fixed server roles in the sense that they can’t be deleted, modified, or changed, but you can add database users and user-defined roles as members. The fixed database roles are:

• db_accessadmin: Can add or remove Windows logins and groups and SQL Server logins in the database.
• db_backupoperator: Can back up the database.
• db_datareader: Can view any data from all user tables in the database.
• db_datawriter: Can add, change, or delete data in all user tables in the database.
• db_ddladmin: Can add, modify, or drop objects in the database. (DDL stands for Data Definition Language, the set of Transact-SQL commands that make structural changes to databases.)
• db_denydatareader: Cannot view any data in the database.
• db_denydatawriter: Cannot change any data in the database.
• db_owner: Can perform the activities of all the database roles as well as maintenance and configuration activities. This role includes all the other roles, so it is essentially an administrator for this database.
• db_securityadmin: Can manage role membership and statement and object permissions in the database.

Fixed database roles can simplify assigning permissions in a database. For example, suppose you want to enable a user to access a particular database only to back it up. You don’t want the user to be able to read the data—just back it up. You can accomplish this easily by making the user a member of the db_backupoperator and the db_denydatareader roles. Use the sp_helprole and sp_helprolemember system stored procedures to view information about database roles.

The Public Role and Guest User

There are a couple of special principals that require mention. You’re not likely to use these principals in any meaningful way, but they do affect security so you need to know what they are.

The public role is a special server role that cannot be dropped. Every database user belongs to this public role, so you don’t need to assign users, groups, or roles to it. Every SQL Server database contains the public role, including master, msdb, tempdb, and model. However, you can grant or restrict the permissions of the public role as your security needs dictate. The important thing to bear in mind about the public role is that permissions you grant to public apply to all database users.

The guest user exists in every database, including the system databases like master and model. As a user, it inherits the permissions of the public role. It comes into play when a server login is not mapped to a user in a particular database. By default, the guest user has no permissions, but you can grant permissions to access database objects and perform actions in the database. As you might expect, this is a highly dangerous thing to do that is rarely necessary in a well-designed security scheme for a database server, and you should avoid assigning permissions to this user. Although you cannot delete this user, you should disable it in user databases by revoking its CONNECT permission using code like that in Listing 3.8.

USE Northwind;
GO
REVOKE CONNECT FROM guest;
GO

Listing 3.8: Code to disable the guest user in a user database by revoking its CONNECT permission.

The dbo User and Schema

dbo is a special user account inside each database that is mapped to the sysadmin fixed server role. What this means is that if you are a member of the sysadmin role and you create an object in any database, then that object’s ownerwill be dbo, not you. You can’t delete the dbo user and it’s mapped only to sysadmin, not to the database owner (db_owner). This can be confusing, since the dbo user really has nothing to do with the db_owner role.

Each database also has a dbo schema owned by the dbo user, and is the default schema for the dbo user. As a result, when you access a database as a sysadmin and create an object without specifying a schema, its two-part name will be dbo.objectname. The dbo schema is also a secondary default schema for any other user when accessing data, if no schema name is specified. If user joe tries to access a table called sales, SQL Server will first check to see if there is a sales table in the default schema for the user joe, and if not, it will check to see if there is a sales table in the dbo schema. Only if sales does not exist in either schema is an error generated that the object can’t be found. Best practice is to always specify a schema name for every object accessed.

User-Defined Database Roles

Database roles aren’t limited to the predefined roles—you can create your own roles. A user can define two types of database roles:

• Standard Role: Use this role to simplify assigning permissions to groups of users. You can nest fixed database roles or other user-defined roles and assign users to the role, in which case they inherit the permissions from the role.
• Application Role: Applications use this role to allow applications, or connections, to log in to a database and activate the application role by supplying the role name and password. You can’t add users to application roles the way you do to other roles, and once activated, the permissions of the application role apply for the duration of the connection. Any individual permissions the users might have are suspended, and only the application role’s permissions are checked.

Securable Objects

A securable object is a protected resource that you can control access to. Usually it is a physical thing, or at least something as physical as a digital object can be! But a securable can also be an action, the ability to make some kind of change to a database or SQL Server instance. For example, an administrator can grant a principal the ability to take ownership of an object. Granting this permission doesn’t immediately change the object’s ownership; it just gives the principal the ability to do it at some future time.

Figure 3.8 shows most of the securable objects in an instance of SQL Server. The server-level securable objects have the broadest scope, encompassing all of SQL Server, including permissions that affect a principal’s ability to make changes to all databases. The database scope encompasses all of the objects in a particular database, such as those used to manage users as well as create encryption keys. The schema scope includes all objects within a schema—essentially the data structure of the database, including tables and their data. A database can contain many schemas, and each can contain a subset of the full set of database objects. What makes schemas powerful is that you can assign and deny permissions on a schema, and those permissions apply to all the objects the schema contains.

Figure 3.8. Securable objects in SQL Server. The arrows show how one scope encompasses a smaller scope in the object hierarchy.

It is important to understand that granting a permission at the server level often implies permissions at a smaller scope. For example, granting a database-level permission may mean that the principal has implied permissions over the objects in one or all of the database’s schemas.

Summary

In this level of the Stairway to SQL Server Security, you learned about the first part of authorization, the principals and securable objects available in an instance of SQL Server and its databases. In the next level, you’ll learn about permissions, which when granted to a principal on a securable object gives or takes away the ability of a principal to do something with the object. With this understanding, you’ll be able to make effective use of the granular nature of authentication and authorization in SQL Server to keep tight control over database assets while allowing authorized users and processes get their work done.