The SQL Server security model, part 1: principals

There are a number of layers in the SQL Server security model, giving you a nearly infinite number of ways to set up access control on your server and databases. Security is a huge topic, and there are literally entire books on it, so this series of articles is designed to give you just a quick overview of the SQL Server security model to get you started.

In this first installment, I’ll go through the different types of security principals that are available, as well as how they connect to each other.

Basic SQL Server security

There are a few concepts you need to be comfortable with when working with SQL Server security. You could think of them as the “subject”, “verb” and “object”:

• Users, logins and roles are referred to as principals. These are the “bearers” of permissions. A principal, the subject of your operation.
• Securables are actual things you want to control access to. From large to small, a server, database, schema, table or even a single column can be a securable. Securables are the “objects”.
• Finally, permissions are the “verbs” that connect the subject to the object – defining what the subject is allowed to do with each object.

So, for instance, if you allow your user John to UPDATE rows in dbo.someTable, John is the principal, dbo.someTable is the securable and UPDATE is the permission. In T-SQL, granting this permission intuitively looks like this:

GRANT UPDATE ON dbo.someTable TO John;

But we’re getting ahead of ourselves. Let’s start off by looking at the different types of security principals available.

Logins and users

First and foremost, you need to be familiar with the concept of logins and users. A login exists on the server level and, like the name implies, is what you use to actually log on to the server. Users, on the other hand, exist in each database and are used to assign specific rights to objects in the database.

Logins and users are two types of security principals; the login is a server principal, the user is a database principal. Every database user is connected to a server login.

Logins come in two forms:

• Integrated security logins (Windows authentication logins) are authenticated against the security database of your Windows environment. Windows logins can be either groups or regular users, in your domain or on your local server.
• SQL Server logins are “simple” logins, with a password stored locally in the SQL Server.

The advantage of Windows authentication is that you don’t have to manage password changes and policies, and it supports single-sign-on, which is very convenient. SQL Server logins are your only option when you cannot (or don’t want to) add an account to your Windows domain. The downside is that you’ll have to manage user and password expirations, password policies, etc.

This query returns all the logins on a server:

--- Server logins:
SELECT principal_id, [name], [type], type_desc, is_disabled
FROM sys.server_principals
WHERE [type] IN ('G', 'S', 'U');

For each database, a login can be given a user. The user is the database principal, so whenever you assign permissions or role memberships in the database, you’re assigning them to the user. In most cases, the user will have the same name as the login, but it doesn’t have to, because they’re technically two separate entities.

Here are all the users in a database:

--- Database users:
SELECT principal_id, [name], [type], type_desc
FROM sys.database_principals
WHERE [type] IN ('G', 'S', 'U');

Users and their corresponding logins are connected with the sid column:

--- Mapping logins to database users:
SELECT sp.principal_id AS server_principal_id,
       dp.principal_id AS [database_principal_id],
       sp.[type], sp.type_desc,
       sp.[name] AS loginName, dp.[name] AS userName,
       sp.is_disabled
FROM sys.database_principals AS dp
INNER JOIN sys.server_principals AS sp ON
    dp.[sid]=sp.[sid];

Database roles

Users can also be members of one or more database roles, which function like traditional user groups. Database roles are also database principals, and as such, you can assign permissions to a database role, and these permissions will apply to all of its members. Any user or other database role can be made a member of a database role.

Database roles, like other database principals are found in the sys.database_principals table:

--- Database roles:
SELECT principal_id, [name], [type], type_desc
FROM sys.database_principals
WHERE [type]='R' AND is_fixed_role=0;

Membership in database roles are found in sys.database_role_members:

--- Database roles and member users:
SELECT role_principal_id, member_principal_id
FROM sys.database_role_members;

Fixed database roles

For each database, there are a number of fixed database roles that you cannot alter. Each of them gives its members a specific set of permissions. I’ll list only the most important ones here:

• db_owner has full administrative permissions in the database (not necessarily on the server). This is like a “local admin” group for a specific database.
• db_securityadmin can manage permissions for all users in the database. In effect, this allows members of db_securityadmin to assign any level of permissions within the database to anybody, including themselves!
• db_datareader has read permissions (SELECT) on all user tables.
• db_datawriter has INSERT, UPDATE and DELETE permissions on all tables.
• db_denydatareader is the opposite of db_datareader – it denies members SELECT access.
• db_denydatawriter denies members INSERT, UPDATE and DELETE access.

For a complete list of fixed database roles, see the documentation on MSDN. Fixed database roles are also found in sys.database_roles:

--- Fixed database roles:
SELECT principal_id, [name], [type], type_desc
FROM sys.database_principals
WHERE [type]='R' AND is_fixed_role=1;

Application roles

Application roles are also database principals to which you can assign permissions. Despite the name, an application role actually behaves more like a user, with its own password, although it is important to note that it isn’t directly associated with a login. Instead, you first have to log on to the server using a login, and then perform a context switch to assume the identity of the application role.

For more on application roles, see a post on database roles that I wrote earlier.

--- Application roles:
SELECT principal_id, [name], [type], type_desc
FROM sys.database_principals
WHERE [type]='A';

Server roles

Just like the fixed database roles, there are fixed server-level roles. These are server-level principals, and membership in them grants a login specific permissions on the entire server. Here are a few of the most important server roles:

• sysadmin can do anything on the server, and has the same access to the operating system as the service account that SQL Server runs on.
• securityadmin can manage server-level permissions for all logins, including assigning database permissions to users. As such, this account should be treated as a sysadmin equivalent.
• public contains every login by default. As a precaution, do not assign any permissions to public.

A comprehensive list of all server-level roles is available on MSDN. Server roles are server-level principals, and as such, found in the sys.server_principals table:

--- Server roles:
SELECT principal_id, [name], [type], type_desc
FROM sys.server_principals
WHERE [type]='R';

Server role memberships can be found in sys.server_role_members:

--- Server roles and associated logins:
SELECT role_principal_id, member_principal_id
FROM sys.server_role_members;

Remember that the principal_id columns in sys.server_role_members are logins, not users! You can map sys.database_principals to sys.server_principals using the sid column.

What happens when you move or restore a database?

When you move a database from one server to another, you can find yourself with orphaned users. This happens when the database principal (the user) doesn’t have a matching server principal (login) on the new server. Recall that users and logins are uniquely identified with a SID, a long binary string. The SID is managed by SQL Server or Active Directory, depending on if the login is a SQL Server login or a Windows login.

If you create a SQL Server login on two different machines, those two logins will get different SIDs. However, if you create a Windows login for a domain account on two different machines, those two logins will have the same SID, because the SID was originally created by the Active Directory. This makes it possible to create orphaned users in a database, if the users were created on one server and the database was then moved to a different server (if you’re using SQL logins or local Windows users/groups) or a different domain (if you’re using Windows domain users/groups).

You may not want to drop and re-create those users, because that would mean re-assigning all the permissions and role memberships on all the new users. Instead, Microsoft provides a handy tool to reconnect the existing orphaned users to logins, in the form of a stored procedure called sp_change_users_logins.

Inheriting permissions from roles

Permissions on a securable (i.e. an object, database, schema, etc) can be explicitly granted or denied to one or more principals (users, roles). The permissions assigned to roles are implicitly inherited to its members, so if you grant SELECT permissions on a table to the database role “business_users”, each member of this role will implicitly have SELECT permissions on that table.

By design, this allows for conflicts in permissions. Say, for instance if a user is a member of two application roles, “developers” and “business_users”. You’ve granted “business_users” SELECT permissions to a table, but you’ve explicitly denied “developers” access to the same table. In such a case, any user who is a member of “developers” will not have SELECT permissions on the table, no matter if he’s allowed by other role memberships.

The principle here is that if any rule denies a user permission, this take precedence over other rules that grant permissions.

What’s next?

We’ve looked at the different types of security principals on the server level and database level, and how they’re connected to each other. In the next installment, we’re going to look at the actual “securables“, i.e. the stuff you can assign permissions on.

In the meantime, make sure you visit and “like” Sunday Morning T-SQL on Facebook, so you’ll get instant notifications of new post!