The Secret of the Security Token – How SQL Server Determines Active Permissions

Introduction

Over the last few of days we have talked about logins, users, server roles, database roles and windows groups. They all contribute to the set of active permissions at the time a query is executed. So, how does SQL Server figure out which permissions to apply? The answer is: Tokens.

Security Tokens

When a connection to a SQL Server instance is established, SQL Server creates a list of login tokes and assigns them to the connection. First it adds a login token for the login that was used. If the login was a windows login, a login token for each windows group that the login is a member of is added to the token list. This step is repeated recursively to capture nested windows groups.

Next step is to add a login token for each server role that the login is a member of. If windows groups were added in the second step, SQL Server checks if there is a login associated with any of them and if so SQL Server adds a token for each server role that any of those group-logins is a member of. As with the windows groups, the server role finding process is repeated recursively.

Every time the connection changes the database context, a list of user tokes is collected. For that SQL Server first checks if the login used has an associated user in the new database. If a user is found a user token for that user is added to the token list.

If a user is found in the previous step, SQL Server also adds a user token for each database role that that user is a member of. As with server roles, the database role finding process is repeated recursively to capture nested database roles.

Each token in those two lists now represents a SQL Server security principal and with each one comes a list of granted or denied permissions. SQL Server now just has to collect all those permissions to get the active permission set.

sys.login_token and sys.user_token

So, what do we have to do to see which tokens are currently active?

We can just select from the sys.login_token and the sys.user_token DMVs. The first returns the list of active login tokens for the current connection and the second returns the list of active user tokens of the current connection in the current database.

To see both of them in action, let's first create a few principals:

The first group of statements creates a login TestLogin and adds it to the freshly created TestServerRole which in turn gets added as a member to the also freshly created server role TestOuterServerRole. The second block of statements creates a user TestUser that is associated with our new login. It also creates two nested database roles TestDatabaseRole and TestOuterDatabaseRole.

If we now connect to SQL Server with the login TestLogin, we can use these two simple select statements to see the active tokens:

The result will look like this:

Besides of the expected six tokens there is also a token for the public database role and the public server roles. Every principal is automatically a member of the public database or server role respectively, so having those two additional tokens in the list makes sense.

Internally, SQL Server stores the tokens just as a list of principal ids. However, the two DMVs return additional information to spare us a join to sys.server_principals or sys.database_principals.

Summary

SQL Server uses a list of security tokens to determine the set of active permissions. Every time a connection is established or the database context is changed, the list of tokens is re-evaluated. We can see the list of active tokens for the current connection by using the sys.login_token and the sys.user_token DMVs.