The SQL Server Permission Hierarchy

A user can have access without a direct GRANT on the table in front of you. The SQL Server permission hierarchy explains how server, database, schema, and object permissions flow.

See the Four Scopes of the Permission Hierarchy

SQL Server permissions attach to securables at different scopes. A server login enters the instance. A database user represents it within a database. A schema groups objects, and an object is a table, view, or procedure. Some permissions are broad enough to affect lower scopes; others are narrow. You need to know the scope before explaining an effective right.

I start with the account and the action. Can this user SELECT a table, EXECUTE a procedure, or change server configuration? Those are different paths through the permission hierarchy. A report of object-level grants alone can miss a schema grant or a role. The hierarchy is a map of where to look, not merely a list of permission names.

Map Login to Database User

A server login can be mapped to a database user. Windows groups and contained users add other routes. A login with access to the instance is not automatically granted access to every user database. Likewise, a user name in a database does not by itself prove a usable server login. Check authentication type and SID mapping where relevant.

I have seen a database restore leave a user name in place while the expected login could not connect. The row looked reassuring until someone tested access. Use the actual identity and database context in permission checks. Names alone can match by coincidence. A successful connection test is stronger evidence than a catalog row.

Review Role Membership

Server roles and database roles grant bundles of rights. Fixed roles have defined behavior; custom roles carry permissions that your team assigned. A user can belong to more than one role, and Windows group membership can add a path. Resolve those paths before concluding that a direct DENY or missing GRANT explains behavior.

The query below lists database role membership in the current database. Repeat the review at server scope for server roles. I keep roles in the application access map because they are the main unit of administration in a least-privilege design.

SELECT role_principal.name AS role_name,
       member_principal.name AS member_name
FROM sys.database_role_members AS drm
JOIN sys.database_principals AS role_principal
  ON role_principal.principal_id = drm.role_principal_id
JOIN sys.database_principals AS member_principal
  ON member_principal.principal_id = drm.member_principal_id
ORDER BY role_principal.name, member_principal.name;

Understand Schema Grants

A schema-level SELECT grant can cover every current and future table in that schema. An object-level grant covers one object. The broad grant is convenient when the schema is designed as an access boundary. It is risky when a sensitive table can later be added there without a separate permission review.

I ask who controls schema deployment when I see a broad grant. The security consequence depends on both current objects and future changes. A permission review should include the scope, not only the word SELECT. The same action at database, schema, and object scope carries very different reach.

Read Explicit Permissions

sys.database_permissions records explicit grants and denies in a database. Join it to principals and securables to make a readable report. Some effective access comes through fixed roles or ownership and will not appear as a simple direct grant for the user. Use catalog output as evidence, then test effective behavior with the intended account.

The query below shows explicit permissions and their class. It is a starting inventory, not a complete effective-permission solver. I review principal and class before proposing a change.

SELECT p.name AS principal_name,
       dp.class_desc,
       dp.permission_name,
       dp.state_desc,
       dp.major_id
FROM sys.database_permissions AS dp
JOIN sys.database_principals AS p
  ON p.principal_id = dp.grantee_principal_id
ORDER BY p.name, dp.class_desc, dp.permission_name;

Treat DENY in the Permission Hierarchy With Care

DENY generally overrides a GRANT through another path, with documented exceptions such as ownership and certain column-level behavior. It can be useful for a narrow restriction, but it makes effective access harder to reason about when scattered widely. Prefer clear role design and narrow grants where possible. Test the actual action as the intended identity.

I have seen a team add more GRANT statements to fix a DENY error. The script grew, but the behavior did not change. Find the denying scope and why it exists. Remove or redesign it only after understanding the original security requirement. More permissions are not always more access.

Remember Ownership and Chains

Object ownership and ownership chaining can let a user execute a procedure that reads a table without direct table permission. EXECUTE AS and certificate signing offer other controlled patterns. Dynamic SQL can break the simple chain and require separate permissions. These mechanisms are useful, but they make a catalog-only review incomplete.

I test the procedure path and the direct table path separately. The application can need one and should be denied the other. An elevated test under sysadmin proves little about the runtime identity. The hierarchy explains why a permission error can appear inside a procedure even when the caller has EXECUTE.

Use Effective-Permission Checks

HAS_PERMS_BY_NAME can answer whether the current execution context has a named permission on a securable. Test under the target identity through an approved method. A returned value of one, zero, or null needs interpretation. Null can mean an invalid securable or permission combination, not a simple denial.

The example checks SELECT on one table in the current database. Replace the object with a real one and run it as the account you are reviewing. Pair the result with an actual harmless SELECT where appropriate.

SELECT HAS_PERMS_BY_NAME
       (N'dbo.YourTable', N'OBJECT', N'SELECT') AS can_select;

Explain an Access Decision Through the Permission Hierarchy

To explain why a user can act, trace the login or contained identity, database user, group and role memberships, explicit permissions, and ownership or execution context. To explain a denial, include the requested action, object, database, and exact error. Keep the report narrow enough to answer the user’s question.

Which path grants this account access today? If you cannot draw it, the permission model needs documentation. The hierarchy becomes practical when it leads to a tested, minimal change. A readable path also makes quarterly reviews faster and makes future restores less surprising.

Related reading on this blog: Understanding Grant, Deny, and Revoke Permissions and Difference Between Login Vs User: Security Concepts.

A direct GRANT is not the whole permission story, it is one path through a layered hierarchy.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.