Troubleshooting SQL Server Permissions

Understanding and troubleshooting SQL Server permissions can be challenging, especially when direct grants, role inheritance, ownership chains, and explicit denies all interact. The six scenarios that follow show how the engine decides who can do what, then demonstrate the diagnostic steps that reveal why it made that decision. Each section provides a setup script you can run in a dedicated test database, followed by a diagnostic query and a short explanation of the result.

Checking effective permissions with sys.fn_my_permissions

Ever wonder what a user can really do? sys.fn_my_permissions reports the exact permissions SQL Server will honor at a given securable level. In the script below we grant rights at the database, schema, and object tiers to see how they combine:

-- Session 1: setup
CREATE USER DemoUser1 WITHOUT LOGIN;

CREATE SCHEMA Sales AUTHORIZATION dbo;

CREATE TABLE Sales.Orders
( OrderID INT PRIMARY KEY,
  CustomerName NVARCHAR(100),
  OrderTotal MONEY );

CREATE PROCEDURE Sales.GetOrders AS
    SELECT * FROM Sales.Orders;
GO

GRANT EXECUTE           TO DemoUser1;                 -- database level
GRANT UPDATE  ON SCHEMA::Sales TO DemoUser1;          -- schema level
GRANT SELECT  ON Sales.Orders TO DemoUser1;
GO

-- Session 2: diagnostics
EXECUTE AS USER = 'DemoUser1';
SELECT * 
FROM sys.fn_my_permissions(NULL, 'DATABASE');         -- database scope
SELECT * 
FROM sys.fn_my_permissions('Sales.Orders', 'OBJECT'); -- object scope
REVERT;

The function shows every permission SQL Server will apply to DemoUser1 at each level, making it the first tool to reach for when a user insists they should have access but something still fails.

Viewing explicit GRANT and DENY entries

When a user’s access does not line up with expectations, start by listing the explicit entries in sys.database_permissions. Remember: a single DENY overrides any number of GRANTs.

CREATE USER DemoUser2 WITHOUT LOGIN;

CREATE TABLE dbo.CustomerData
( CustomerID INT,
  Name       NVARCHAR(100) );

GRANT SELECT ON dbo.CustomerData TO DemoUser2;
DENY  INSERT ON dbo.CustomerData TO DemoUser2;

SELECT  dp.name          AS PrincipalName,
        o.name           AS ObjectName,
        o.type_desc      AS ObjectType,
        p.permission_name,
        p.state_desc     AS PermissionState
FROM    sys.database_permissions AS p
JOIN    sys.database_principals  AS dp ON p.grantee_principal_id = dp.principal_id
LEFT JOIN sys.objects            AS o  ON p.major_id = o.object_id
WHERE   dp.name = 'DemoUser2';

You will see both the GRANT SELECT and the explicit DENY INSERT. If DemoUser2 tries an INSERT, the operation fails even though SELECT succeeds—exactly the behaviour you would troubleshoot in production.

Tracing role-based inheritance

Roles simplify day-to-day administration but complicate diagnostics because permissions flow indirectly. The next script confirms role membership and then impersonates the user to prove that the inherited rights take effect.

CREATE USER DemoUser3  WITHOUT LOGIN;
CREATE ROLE SalesReader;

CREATE TABLE dbo.SalesData
( SaleID INT PRIMARY KEY,
  ProductName NVARCHAR(100),
  Amount MONEY );

GRANT SELECT ON dbo.SalesData TO SalesReader;
ALTER ROLE SalesReader ADD MEMBER DemoUser3;
GO

SELECT r.name AS RoleName, m.name AS MemberName
FROM   sys.database_role_members rm
JOIN   sys.database_principals r ON rm.role_principal_id  = r.principal_id
JOIN   sys.database_principals m ON rm.member_principal_id = m.principal_id
WHERE  m.name = 'DemoUser3';

EXECUTE AS USER = 'DemoUser3';
SELECT * FROM sys.fn_my_permissions('dbo.SalesData', 'OBJECT'); -- shows SELECT
REVERT;

If a query works during impersonation, you know the missing permission is not the problem; look for other causes such as schema mismatches or explicit denies.

Server-level permissions that affect every database

Some rights—VIEW ANY DATABASE, ALTER ANY LOGIN and so on—live at the server scope. They do not appear in database views and can therefore be missed.

CREATE LOGIN DemoLogin1 WITH PASSWORD = 'StrongP@ssword123!';
GRANT VIEW ANY DATABASE TO DemoLogin1;
GO

SELECT  sp.name, sp.type_desc,
        p.permission_name, p.state_desc
FROM    sys.server_permissions  AS p
JOIN    sys.server_principals   AS sp ON p.grantee_principal_id = sp.principal_id
WHERE   sp.name = 'DemoLogin1';

VIEW ANY DATABASE lets a login see all database names in metadata queries even if it cannot connect to them—useful for monitoring accounts, surprising when overlooked.

When DENY counters a role’s GRANT

To illustrate precedence rules, create a role that grants SELECT and then deny that same permission directly to the user. The role grant becomes irrelevant.

CREATE USER DemoUser5 WITHOUT LOGIN;
CREATE ROLE DataReaders;

CREATE TABLE dbo.FinancialData
( RecordID INT,
  Revenue  MONEY );

GRANT SELECT ON dbo.FinancialData TO DataReaders;
ALTER ROLE DataReaders ADD MEMBER DemoUser5;
DENY  SELECT ON dbo.FinancialData TO DemoUser5;
GO

EXECUTE AS USER = 'DemoUser5';
SELECT * FROM dbo.FinancialData;   -- fails
REVERT;

Whenever a user belongs to the right role yet still encounters an error, scan their explicit entries for a deny before assuming something is wrong with the role itself.

Ownership chaining and broken chains

Objects owned by the same principal form an ownership chain; permission is checked only at the top. Changing ownership mid-chain forces SQL Server to check every link.

CREATE USER DemoUser6 WITHOUT LOGIN;
CREATE SCHEMA Sales2 AUTHORIZATION dbo;

CREATE TABLE Sales2.Orders
( OrderID INT,
  CustomerName NVARCHAR(100) );

CREATE PROCEDURE Sales2.GetOrders AS
    SELECT * FROM Sales2.Orders;
GO

GRANT EXECUTE ON Sales2.GetOrders TO DemoUser6;
GO

EXECUTE AS USER = 'DemoUser6';
EXEC   Sales2.GetOrders;          -- succeeds
REVERT;

CREATE USER AltOwner WITHOUT LOGIN;
ALTER AUTHORIZATION ON SCHEMA::Sales2 TO AltOwner;
GO

EXECUTE AS USER = 'DemoUser6';
EXEC   Sales2.GetOrders;          -- now fails
REVERT;

SELECT  s.name   AS SchemaName,
        dp.name  AS OwnerName
FROM    sys.schemas            AS s
JOIN    sys.database_principals dp ON s.principal_id = dp.principal_id
WHERE   s.name = 'Sales2';

If a stored procedure loses access after a deployment, confirm whether its schema or an underlying object was reassigned to a different owner.

Closing remarks

These six demonstrations show the key influences on SQL Server’s permission engine: direct grants and denies, role inheritance, server-level rights, and ownership chains. By pairing a concise setup with targeted diagnostic queries, you can reproduce—and then solve—most real-world access problems in minutes. Keep sys.fn_my_permissions, the catalog views for principals and permissions, and EXECUTE AS in your troubleshooting toolkit; they reveal exactly why the engine allows or blocks a given action.