SQL Server Permissions Without the Guesswork

SQL Server permissions become easier to understand when you separate who is acting from what is being accessed. Roles and ownership chains then explain why a direct table grant is not always required.

Name the Actor and the Resource

A principal is an identity that can receive permissions, such as a login, user, or role. A securable is something protected, such as a server, database, schema, or object. A permission describes an allowed or denied action on that resource.

SELECT ORIGINAL_LOGIN() AS original_login,
       SUSER_SNAME() AS current_login,
       USER_NAME() AS current_database_user,
       DB_NAME() AS database_name;
SELECT name, type_desc, authentication_type_desc
FROM sys.database_principals
WHERE principal_id > 4
ORDER BY name;

The login and database user are related but distinct. A successful connection does not automatically provide access to every database. Contained users also change how authentication relates to the instance.

Begin an investigation in the database where the operation failed. Record the account used by the actual application. Your administrator session can hide the very permission problem you are trying to reproduce.

Use Roles to Describe Responsibilities

A role groups permissions for a responsibility shared by one or more users. It is easier to review a reporting role than scattered grants across many accounts. Keep the role narrow enough that its name still explains its purpose.

SELECT r.name AS role_name, m.name AS member_name
FROM sys.database_role_members AS rm
JOIN sys.database_principals AS r
  ON r.principal_id = rm.role_principal_id
JOIN sys.database_principals AS m
  ON m.principal_id = rm.member_principal_id
ORDER BY r.name, m.name;

Fixed roles can grant broad capabilities that do not appear as ordinary explicit permission rows. Nested roles and Windows group membership can add another layer. A membership listing is therefore one part of the explanation, not a complete effective-access report.

Avoid assigning db_owner simply because a deployment failed once. Find the required operation and separate deployment access from runtime access. Otherwise, a temporary workaround becomes the application’s permanent authority.

Read Grants at Their Actual Scope

Permissions can apply at different levels. A schema-level grant can cover objects inside that schema, including future objects where the permission applies. That can be convenient, but it also makes schema ownership and deployment conventions important.

SELECT USER_NAME(grantee_principal_id) AS grantee,
       state_desc, permission_name, class_desc,
       major_id, minor_id
FROM sys.database_permissions
ORDER BY grantee, class_desc, permission_name;

Interpret major_id and minor_id using class_desc. An object identifier and a schema identifier occupy different permission classes. Treating every identifier as an object name can produce a misleading report.

DENY generally takes precedence over a conflicting grant, but SQL Server has documented exceptions and bypass rules. Owners, sysadmin, and column-level behavior require care. Do not build an access conclusion from one simplified slogan.

Understand Why a View Grant Can Be Enough

Suppose a view exposes selected columns from a table, and both objects have the same effective owner. With an unbroken ownership chain, SQL Server can check access to the view without checking the underlying table permission. This supports a controlled interface.

The chain depends on ownership and the kind of statement involved. Different owners can require additional checks. Dynamic SQL does not inherit the ordinary static ownership-chain behavior simply because a procedure constructed it.

SELECT s.name AS schema_name, o.name, o.type_desc,
       USER_NAME(COALESCE(o.principal_id, s.principal_id)) AS effective_owner
FROM sys.objects AS o
JOIN sys.schemas AS s ON s.schema_id = o.schema_id
WHERE o.type IN ('U', 'V', 'P') AND o.is_ms_shipped = 0
ORDER BY s.name, o.name;

This query helps inspect ownership, but it does not prove every execution path is authorized. Review referenced objects and dynamic statements as well. Avoid enabling broad cross-database ownership chaining as a casual repair.

Ask What the Current Context Can Do

Effective permissions are usually more useful than isolated grants when explaining a failure. Query them under the intended security context. Start at database scope, then inspect the exact object involved.

SELECT permission_name
FROM sys.fn_my_permissions(NULL, 'DATABASE')
ORDER BY permission_name;
SELECT HAS_PERMS_BY_NAME(DB_NAME(), 'DATABASE', 'CREATE TABLE')
       AS can_create_table;

For an object-specific check, use its actual schema-qualified name with fn_my_permissions or HAS_PERMS_BY_NAME. A NULL result can reflect an invalid or invisible securable. Do not report that as an ordinary zero without investigating.

Test Both Permission Boundaries

A useful access test includes one required operation and one prohibited operation. Run the real application path where possible, including procedures and dynamic statements. Testing a direct SELECT alone can miss the intended interface.

Save the reason for each grant and identify its owner. Revisit access when the application changes or an account is retired. Permission design stays understandable when the business responsibility remains visible.

Permission troubleshooting is not adding bigger roles, it is tracing the actual access path.

This post was rewritten from scratch in September 2026. The original, published on 2007-11-08, was a short announcement about something that no longer exists. The address is the same, the subject is now something worth keeping.

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