SQL Server Database Users and the sys.database_principals catalog view

Introduction

Yesterday we took a look at SQL Server's server principals. Today we are going to look at the second level of the authentication hierarchy: The database users or database principals.

Database principals cannot be used to directly connect to SQL Server. (SQL Server 2012 introduced contained databases that change this rule a little. We will discuss contained databases in a later post.) However, they can be used to grant or deny permissions on objects within a particular database.

Logins and Users

When a login is created on a SQL Server instance, it does not have a lot of permissions out of the box. It can connect to the instance, but it cannot access any user database. To allow access to a user database a user needs to exist in that database that is linked to that same login. (There are exceptions to this rule if the login is a member of a server role with elevated permissions like the sysadmin fixed server role.)

During the creation of a database user a login can be specified. The user will then be linked to that login. That means that any connection established with that login will be able to connect to the database that this user was created in and access objects based on the permissions granted to that user.

Listing all database users

To list all database users the sys.database_principals catalog view can be utilized:

The column list is very similar to the one of the sys.server_principals catalog view that we discussed yesterday. Particularly interesting is the SID column as it allows us to find the linked server principal:

The JOIN has to be an outer join as you might have users that are not liked to a login.

Database Principal Types

As with server principals, there are three main types of database principals: users, groups and certificate based principals. Users are used to grant database level permissions to a linked login. They can be based on a SQL Login or on a Windows Login.

Database principals can be linked to a server principal that represents a windows group. On the other hand, Server roles cannot be used to create a user. Their purpose is exclusively to ease management of server level permissions. Instead there is a database role. Database roles also are not based on a login as their sole purpose is to group several users of the database together to simplify database level permissions.

Additionally to those two group type principals there is a third one: The application role. Application roles will be discussed in a later article.

Like server principals, database principals can be created using a certificate or asymmetric key. Those principals can be used to grant database level permissions to T-SQL modules. How this works exactly I will demonstrate in a separate post.

To find out the type of a given user you can consult the type_desc column of the sys.database_principals catalog view.

Summary

Database principals are defined within a specific database and can be used to grant database level permissions. There are seven types of databases principals that can be categorized in three main categories: Users, groups and certificate base principals.

A login can be linked to a database user and any connection using that login will get the permissions granted to its linked user while accessing objects in that database.

The sys.database_principals catalog view returns one row for each database principal defined in the current database.