SQL Server Permissions and Ownership Chaining

Introduction

Designing a reporting layer that protects sensitive data takes more than hiding tables behind a view. You must understand how schema permissions and ownership chaining interact, or a well‑meant deny can suddenly block your users—or worse, let them see columns you thought were private. The walk‑through below shows the entire life‑cycle of a common scenario:

1. Build an HR table that holds confidential columns.
2. Expose a summary view in a separate schema.
3. Grant a reporting role access to the view but explicitly deny access to the HR schema.
4. Break the ownership chain, observe the failure, diagnose the cause, and repair it by realigning ownership.

All scripts were tested on Azure SQL Database and work unchanged on SQL Server.

1 Set up the objects

-- Create logical containers
CREATE SCHEMA HR;
CREATE SCHEMA SalesReporting;

-- HR table with sensitive data
CREATE TABLE HR.Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName  NVARCHAR(50),
    LastName   NVARCHAR(50),
    Department NVARCHAR(50),
    HireDate   DATE,
    SSN        CHAR(11),
    Salary     DECIMAL(10,2)
);

INSERT INTO HR.Employees VALUES
(1,'Alice','Smith','Sales','2020-01-15','123-45-6789',75000),
(2,'Bob','Jones','HR',   '2019-03-10','987-65-4321',85000),
(3,'Carol','Lee', 'Sales','2021-07-01','111-22-3333',72000);

-- Read‑only view for the sales team
CREATE VIEW SalesReporting.vw_EmployeeSummary
WITH SCHEMABINDING AS
SELECT EmployeeID, FirstName, LastName, Department, HireDate
FROM HR.Employees
WHERE Department = 'Sales';

2 Create a reporting role and user

CREATE ROLE SalesTeamRole;
CREATE USER  SalesUser WITHOUT LOGIN;
ALTER ROLE SalesTeamRole ADD MEMBER SalesUser;

DENY  SELECT ON SCHEMA::HR             TO SalesTeamRole;
GRANT SELECT ON SCHEMA::SalesReporting TO SalesTeamRole;

The role can query anything under SalesReporting but is denied direct access to anything in HR.

3 Verify the design

EXECUTE AS USER = 'SalesUser';
SELECT * FROM SalesReporting.vw_EmployeeSummary;  -- succeeds
SELECT * FROM HR.Employees;                       -- fails (Msg 229)
REVERT;

Because both the view and the table live in schemas owned by dbo, SQL Server does not re‑check permissions on the base table; the deny on HR is respected only for direct queries.

4 Break the ownership chain

CREATE SCHEMA SalesReporting2 AUTHORIZATION db_owner;

GRANT SELECT ON SCHEMA::SalesReporting2 TO SalesTeamRole;

CREATE VIEW SalesReporting2.vw_EmployeeSummary
WITH SCHEMABINDING AS
SELECT EmployeeID, FirstName, LastName, Department, HireDate
FROM HR.Employees
WHERE Department = 'Sales';

EXECUTE AS USER = 'SalesUser';
SELECT * FROM SalesReporting2.vw_EmployeeSummary; -- Msg 229
REVERT;

Because SalesReporting2 belongs to db_owner while HR belongs to dbo, the chain is broken and SQL Server demands rights on the underlying table. The explicit deny wins and the query fails.

5 Diagnose with system metadata

SELECT s.name   AS SchemaName,
       dp.name  AS Owner
FROM sys.schemas s
JOIN sys.database_principals dp ON s.principal_id = dp.principal_id
WHERE s.name LIKE 'SalesReporting%';

EXECUTE AS USER = 'SalesUser';
SELECT * FROM fn_my_permissions('SalesReporting2','SCHEMA'); -- only SELECT
REVERT;

Ownership misalignment is now obvious.

6 Repair the chain and restore access

ALTER AUTHORIZATION ON SCHEMA::SalesReporting2 TO dbo;

-- Ownership changed; the previous SELECT grant is still valid, so test again
EXECUTE AS USER = 'SalesUser';
SELECT * FROM SalesReporting2.vw_EmployeeSummary;  -- now succeeds
REVERT;

Realigning schema ownership re‑establishes the chain, and the original deny on HR still blocks direct access to the table.

7 Clean‑up script (optional)

IF USER_NAME() = 'SalesUser' REVERT;
DROP VIEW   IF EXISTS SalesReporting2.vw_EmployeeSummary;
DROP VIEW   IF EXISTS SalesReporting.vw_EmployeeSummary;
DROP TABLE  IF EXISTS HR.Employees;
DROP USER   IF EXISTS SalesUser;
DROP ROLE   IF EXISTS SalesTeamRole;
DROP SCHEMA IF EXISTS SalesReporting2;
DROP SCHEMA IF EXISTS SalesReporting;
DROP SCHEMA IF EXISTS HR;

Closing thoughts

A reporting layer that protects sensitive data hinges on two ideas: grant at the schema level and keep ownership aligned. When a view and its base table share the same owner, SQL Server trusts the relationship and skips extra checks, so a deny on the underlying schema does not leak through the view. Change ownership—even unintentionally—and the chain breaks, surfacing permission errors or, worse, exposing data you thought was hidden.

Whenever you add a new schema or move objects, verify ownership, re‑test impersonated access, and document the rationale. A few minutes of due diligence ensures that your least‑privilege design stays intact as the database evolves.