Security Related Catalog Views

This section of the SQL Server documentation provides an overview and detailed information about system catalog views that are specifically designed to help you manage and query security-related information within your SQL Server instances.

These views offer insights into principals (logins, users, roles), permissions, auditing settings, encryption, and other security configurations. Understanding these views is crucial for database administrators and security professionals to ensure data integrity, control access, and maintain compliance.

Key Security Concepts

Before diving into specific views, it's helpful to understand some core security concepts in SQL Server:

  • Principals: Entities that can request SQL Server resources, such as logins (server-level) and users (database-level).
  • Permissions: The rights granted to principals to perform specific actions on securable objects (e.g., SELECT on a table, EXECUTE on a stored procedure).
  • Securables: Objects within SQL Server that have permissions associated with them.
  • Roles: Collections of permissions that can be assigned to principals.

Commonly Used Security Catalog Views

Principals and Logins

These views provide information about server and database principals.

  • sys.server_principals: Returns a row for each security principal on the server.
  • sys.database_principals: Returns a row for each security principal in the current database.
  • sys.sql_logins: Returns one row for each SQL Server login.
  • sys.server_role_members: Shows membership in server-level roles.
  • sys.database_role_members: Shows membership in database-level roles.

Permissions

These views describe the permissions granted or denied to principals.

Catalog View Description
sys.database_permissions Returns a row for each permission granted or denied on a securable in the database.
sys.server_permissions Returns a row for each permission granted or denied on a securable on the server.
sys.object_privileges Returns a row for each permission for an object in the database. (Deprecated in favor of sys.database_permissions)

Auditing and Logging

Views related to SQL Server Audit and Extended Events.

  • sys.server_audits: Returns a row for each server audit object.
  • sys.database_audits: Returns a row for each database audit object.
  • sys.event_sessions: Returns a row for each Extended Events session.

Encryption and Masking

Information about Transparent Data Encryption (TDE), Always Encrypted, and Dynamic Data Masking.

  • sys.certificates: Returns a row for each certificate in the database.
  • sys.symmetric_keys: Returns a row for each symmetric key.
  • sys.dm_database_encryption_keys: Returns information about the encryption keys used in the current database.
  • sys.masked_columns: Returns information about columns that are masked by Dynamic Data Masking.

Example Query: Finding Database Users and Their Permissions

The following query demonstrates how to retrieve a list of database users and the permissions they have on securable objects within the current database.


SELECT
    dp.name AS DatabaseUserName,
    dp.type_desc AS UserType,
    dp.is_disabled,
    perm.permission_name,
    obj.name AS ObjectName,
    obj.type_desc AS ObjectType
FROM
    sys.database_principals AS dp
LEFT JOIN
    sys.database_permissions AS perm ON dp.principal_id = perm.grantee_principal_id
LEFT JOIN
    sys.objects AS obj ON perm.major_id = obj.object_id
WHERE
    dp.type_desc IN ('SQL_USER', 'WINDOWS_USER', 'EXTERNAL_USER')
    AND dp.principal_id NOT IN (SELECT principal_id FROM sys.database_role_members WHERE role_principal_id = (SELECT principal_id FROM sys.database_principals WHERE name = 'db_owner'))
ORDER BY
    dp.name, obj.name;

Further Information

For more in-depth details on each specific catalog view, including columns, data types, and usage examples, please refer to the individual view documentation linked in the sidebar.

Related Topics: