Security cache concepts

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

This article explains how SQL Server uses a security cache to validate permissions a principal has to access securables.

Purpose

The Database Engine organizes a hierarchical collection of entities, known as securables, which can be secured with permissions. The most prominent securables are servers and databases, but permissions can also be set at a finer level. SQL Server controls the actions of principals on securables by ensuring they have the appropriate permissions.

The following diagram shows that a user, Alice, has a login at the server level, and a three different users mapped to the same login at each different database.

To optimize the authentication process, SQL Server uses a security cache.

No cache workflow

When the security cache is invalid, SQL Server follows a no cache workflow to validate permissions. This section describes the no cache workflow.

To demonstrate, consider the following query:

SELECT t1.Column1,
       t2.Column1
FROM Schema1.Table1 AS t1
     INNER JOIN Schema2.Table2 AS t2
         ON t1.Column1 = t2.Column2;

When the security cache is invalid, the service completes the steps described in the following workflow before it resolves the query.

For SQL Server the tasks without a security cache include:

1. Connect to the instance.
2. Perform login validation.
3. Create the security context token and login token. Details of these tokens are explained in the next section.
4. Connect to the database.
5. Create a database user token inside the database.
6. Check the membership of database roles. For example, db_datareader, db_datawriter, or db_owner.
7. Verify user permissions on all columns, for example, the permissions of the user on t1.Column1 and t2.Column1.
8. Checks user permissions on all tables, such as table1 and table2, and schema permissions on Schema1 and Schema2.
9. Verifies database permissions.

SQL Server repeats the process for every single role that the user belongs to. Once all permissions are obtained, the server performs a check to ensure that the user has all the necessary grants in the chain and not a single deny in the chain. After the permission check is complete, the query execution begins.

For more information, review Summary of the permission check algorithm.

To simplify validation, SQL Server uses a security cache.

Security cache definition

The security cache stores permissions for a user or a login for various securable objects in a database or server. One of the benefits is that it speeds up query execution. Before SQL Server executes a query, it checks if the user has the necessary permissions for different database securables, such as schema-level permissions, table-level permissions, and column permissions.

Security cache objects

To make the workflow explained in the previous section faster, SQL Server caches many different objects inside security caches. Some of the objects that are cached include:

Security cache stores

The tokens are stored inside different cache stores.

Known issues

This section describes issues with the security cache.

Security cache invalidations

Various scenarios can trigger security cache invalidations at either the database or server level. When an invalidation occurs, all current cache entries are invalidated. All future queries and permission checks follow the full "No cache" workflow until the caches are repopulated. Invalidation can significantly impact server performance, especially under high load, as all active connections need to rebuild the cached entries. Repeated cache invalidations can make this impact worse. Invalidations in the master database are treated as server-wide invalidations, affecting the caches in all databases on the instance.

SQL Server 2025 introduces a feature that invalidates caches for only a specific login. This means that when security cache entries are invalidated, only those entries belonging to the affected login are affected. For instance, if you grant login L1 a new permission, the tokens for login L2 remain unaffected.

As an initial step, this feature applies to the CREATE, ALTER and DROP login scenarios, and permission changes for individual logins. Group logins continue to experience server-level invalidation.

The table below lists all security Data Definition Language (DDL) actions that invalidate the security cache.

Query performance when the size of TokenAndPermUserStore grows

Performance issues, such as high CPU usage and increased memory consumption, can be caused by excessive entries in the TokenAndPermUserStore cache. By default, SQL Server only cleans up entries in this cache when it detects internal memory pressure. However, on servers with plenty of RAM, internal memory pressure might not occur frequently. As the cache grows, the time required to search for existing entries to reuse increases. This cache is managed by a spinlock, allowing only one thread to perform the search at a time. Consequently, this behavior can lead to decreased query performance and higher CPU usage.

Workaround

SQL Server provides two trace flags (TF) that can be used to set a quota for the TokenAndPermUserStore cache. By default, there's no quota, meaning the cache can hold an unlimited number of entries.

• TF 4618: Limits the number of entries in the TokenAndPermUserStore to 1024.
• TF 4618 and TF 4610: Limits the number of entries in the TokenAndPermUserStore to 8192. If the low entry count limit of TF 4618 causes other performance issues, it's recommended to use trace flags 4610 and 4618 together. For more information, see DBCC TRACEON - Trace Flags (Transact-SQL).

For more information, you can refer to the article Performance issues can be caused by excessive entries in the TokenAndPermUserStore cache - SQL Server

Best practices

This section lists best practices to optimize security workflow.

User management during nonpeak hours

Given the high-level invalidation nature of security caches (database/server level), perform security DDLs during nonbusiness hours when the server load is low. If a security cache invalidation occurs during heavy workloads, there can be a noticeable performance impact on the entire server as the security caches are repopulated.

Use single transactions for permission modifications

Performing multiple security Data Definition Language (DDL) operations within the same transaction can significantly increase the chance of encountering deadlocks with other active connections To mitigate this risk, it's recommended to avoid executing multiple security DDLs in a single transaction. Instead, execute security-related DDL operations in separate transactions to minimize lock contention.

Related content

• Get started with Database Engine permissions