The actual set of permissions a SQL Server principal (login or user) has at runtime, calculated by combining explicitly granted permissions, inherited role memberships, and any applicable DENY statements.
- SQL Server computes effective permissions by evaluating: Object-level permissions, schema-level permissions, database role memberships, server role memberships, and ownership chains.
- DENY always overrides GRANT: A user denied a permission through any path cannot access the resource, regardless of other granted permissions.
- DBAs can check effective permissions using:
fn_my_permissions()(for the current user),sys.fn_builtin_permissions(), or the Effective Permissions dialog in SSMS. - Understanding effective permissions is critical for implementing least-privilege access: Least privilege is a core principle of database security.
- Misconfigurations are common: Users may have more access than intended through nested role memberships or schema-level grants.
- Relevant Idera tools: SQL Secure analyzes and reports on effective permissions across all SQL Server instances, identifying over-privileged accounts and security policy violations.
- Related terms: Permissions, Roles, Active Directory, Row-Level Security, Least Privilege.
