Role-Based Access Demo

Create logins, users, and permissions so different roles see only their own data.

SecurityPermissionsViews

Overview

Most real applications have more than one kind of user, and not every user should see every row. A manager in the Sales department should not be able to browse HR salary data, and an HR user should not need Sales pipeline numbers — but both of them might query the exact same underlying `employees` table. SQL Server gives you two complementary tools for this: object-level security (`CREATE LOGIN`, `CREATE USER`, `GRANT`/`DENY`, controlling *which tables and views* a user can touch at all) and views that filter rows, restricting *which rows within a table* a user's queries can ever see. This project builds both, together.

By the end of this tutorial you will have created a SQL Server login and a database user mapped to it, a `sales_dept_view` that filters `employees` down to a single department using `SESSION_CONTEXT()`, a database role with `GRANT SELECT` on the view and an explicit `DENY SELECT` on the underlying table, and you will have seen why a Sales-department demo user can query the view but is blocked outright from querying `employees` directly.

What You'll Build

An `employees` table spanning two departments, Sales and HR.
A `CREATE LOGIN` and a `CREATE USER` mapping a demo login into the database.
A `sales_dept_view` that filters `employees` to one department using `SESSION_CONTEXT()`.
A `CREATE ROLE` with `GRANT SELECT` on the view and `DENY SELECT` on the base table.
A demonstration that the Sales demo user sees only Sales rows through the view, and nothing through the table.

Prerequisites

CREATE TABLE basics in T-SQL and basic INSERT/SELECT statements.
The general idea of a `VIEW` — a stored query that can be selected from like a table.
The difference between a SQL Server login (instance-level) and a database user (database-level).
The basic idea of `GRANT` and `DENY` — allowing or explicitly blocking a permission.
Comfort running T-SQL as an administrator in SQL Server Management Studio or Azure Data Studio.

Project Structure

One table, `employees`, holds everyone's data across both departments. Everything else in this project is *security scaffolding* around that one table: a view that filters it, a role that groups permissions, and a login/user pair that represents a real person signing in as a member of that role.

Object	Purpose	Key Details
employees	One row per employee, across all departments	employee_id (PK), department, full_name, salary
sales_dept_view	Filters employees down to the Sales department only	Uses SESSION_CONTEXT(N'dept') to decide which rows to show
sales_role	Database role granted access to the view only	GRANT SELECT ON sales_dept_view; DENY SELECT ON employees
sales_demo_login / sales_demo_user	A demo login/user added as a member of sales_role	Represents a real Sales-department employee signing in

Step 1: Create the Employees Table

A straightforward table with one column, `department`, that everything downstream filters on. `salary` is included deliberately — it is exactly the kind of column a real company would not want every department seeing for every other department, which is what makes this a believable security demo rather than an abstract one.

CREATE TABLE employees (
    employee_id INT IDENTITY(1,1) PRIMARY KEY,   -- Surrogate key
    full_name NVARCHAR(100) NOT NULL,
    department VARCHAR(20) NOT NULL,             -- 'Sales' or 'HR' -- the column every access rule below filters on
    salary DECIMAL(10,2) NOT NULL,               -- Sensitive: exactly the kind of data that shouldn't cross departments
    CONSTRAINT CK_employees_department CHECK (department IN ('Sales', 'HR'))
);

Step 2: Insert Sample Data Across Two Departments

Three Sales employees and two HR employees, so the department-scoped view built in Step 4 has a real, visible difference to demonstrate: a Sales user should see exactly the three Sales rows and none of the HR rows.

INSERT INTO employees (full_name, department, salary) VALUES
('Rohan Malhotra', 'Sales', 62000.00),
('Divya Menon', 'Sales', 58000.00),
('Farhan Ali', 'Sales', 65000.00),
('Kavita Rao', 'HR', 71000.00),
('Suresh Pillai', 'HR', 69000.00);

Step 3: Create Logins and Database Users

A SQL Server *login* authenticates at the server (instance) level — it is who you are when you connect at all. A database *user* is a separate mapping inside one specific database that says what that login is allowed to do there. The two are created and linked deliberately in two steps, because the same login can be mapped to different users (with different permissions) in different databases on the same server.

-- Server-level: authenticates the connection
CREATE LOGIN sales_demo_login WITH PASSWORD = 'Str0ng!DemoPassw0rd';

-- Database-level: maps that login into THIS database with its own identity
CREATE USER sales_demo_user FOR LOGIN sales_demo_login;

Demo password only

The password above is illustrative only — a real deployment would use a generated secret, enforce a password policy, and never hardcode credentials in a script checked into source control.

Step 4: Create a Department-Scoped View

`SESSION_CONTEXT(N'dept')` reads a key/value pair set earlier in the same session with `sp_set_session_context` — it lets one shared view definition filter differently depending on who is currently connected, instead of hardcoding `WHERE department = 'Sales'` into a view that only Sales could ever use. In a real application, the app's connection layer would call `sp_set_session_context` immediately after a user authenticates, using their actual department; here it is set manually to demonstrate the mechanism.

CREATE VIEW sales_dept_view AS
SELECT employee_id, full_name, department, salary
FROM employees
WHERE department = CAST(SESSION_CONTEXT(N'dept') AS VARCHAR(20));
-- SESSION_CONTEXT(N'dept') is set per-session (see below) — the view itself
-- never hardcodes 'Sales', so the same view definition scopes correctly
-- no matter which department's session sets the context key.

-- Simulating what the application layer would do right after a Sales user authenticates
EXEC sp_set_session_context @key = N'dept', @value = N'Sales';

SELECT * FROM sales_dept_view;

employee_id	full_name	department	salary
1	Rohan Malhotra	Sales	62000.00
2	Divya Menon	Sales	58000.00
3	Farhan Ali	Sales	65000.00

Step 5: Create a Role and Grant/Deny Permissions

A database role is a named group of permissions that users can be added to, so permissions are managed once per role instead of once per individual user. `GRANT SELECT ON sales_dept_view` allows the role to query the filtered view. `DENY SELECT ON employees` is what makes this a real security boundary and not just a convenience: `DENY` in SQL Server always wins over any `GRANT`, even one inherited from another role, so a member of `sales_role` is blocked from querying `employees` directly no matter what other permissions they might pick up elsewhere.

CREATE ROLE sales_role;

GRANT SELECT ON sales_dept_view TO sales_role;   -- Allowed: the filtered view
DENY SELECT ON employees TO sales_role;          -- Explicitly blocked: the raw, unfiltered table

ALTER ROLE sales_role ADD MEMBER sales_demo_user;

-- Querying as sales_demo_user (e.g. via EXECUTE AS USER = 'sales_demo_user')
SELECT * FROM sales_dept_view;   -- Succeeds: returns only the 3 Sales rows shown in Step 4

SELECT * FROM employees;         -- Fails: blocked by the explicit DENY

Error When Querying employees Directly

Click Run to see what this code prints.

Complete Schema

The full schema and security setup, ready to run top to bottom.

CREATE TABLE employees (
    employee_id INT IDENTITY(1,1) PRIMARY KEY,
    full_name NVARCHAR(100) NOT NULL,
    department VARCHAR(20) NOT NULL,
    salary DECIMAL(10,2) NOT NULL,
    CONSTRAINT CK_employees_department CHECK (department IN ('Sales', 'HR'))
);
GO

CREATE LOGIN sales_demo_login WITH PASSWORD = 'Str0ng!DemoPassw0rd';
CREATE USER sales_demo_user FOR LOGIN sales_demo_login;
GO

CREATE VIEW sales_dept_view AS
SELECT employee_id, full_name, department, salary
FROM employees
WHERE department = CAST(SESSION_CONTEXT(N'dept') AS VARCHAR(20));
GO

CREATE ROLE sales_role;
GRANT SELECT ON sales_dept_view TO sales_role;
DENY SELECT ON employees TO sales_role;
ALTER ROLE sales_role ADD MEMBER sales_demo_user;

Sample Queries

Two more queries worth running: confirming exactly what `sales_role` can and cannot touch, and how the view behaves when the session context is switched to HR instead.

-- As an administrator: list every effective permission sales_role actually has
SELECT
    pr.permission_name,
    pr.state_desc,
    obj.name AS object_name
FROM sys.database_permissions pr
JOIN sys.objects obj ON pr.major_id = obj.object_id
WHERE pr.grantee_principal_id = DATABASE_PRINCIPAL_ID('sales_role');

permission_name	state_desc	object_name
SELECT	GRANT	sales_dept_view
SELECT	DENY	employees

-- Same view, different session context: switching 'dept' to HR changes what the view returns,
-- proving the filter lives in SESSION_CONTEXT(), not hardcoded in the view definition
EXEC sp_set_session_context @key = N'dept', @value = N'HR';

SELECT * FROM sales_dept_view;

employee_id	full_name	department	salary
4	Kavita Rao	HR	71000.00
5	Suresh Pillai	HR	69000.00

Extend This Project

Add an `hr_role` mirroring `sales_role`, granted a symmetrical `hr_dept_view` instead.
Replace the manual `sp_set_session_context` call with SQL Server Row-Level Security (`CREATE SECURITY POLICY`) for a built-in equivalent.
Add a `column_encryption` demo on `employees.salary` using Always Encrypted, layering encryption on top of the access controls here.
Add an `audit_log` table and a `DDL`/`DML` trigger recording who queried `employees` and when it was denied.
Create a third role, `readonly_admin_role`, granted `SELECT` on `employees` directly but nothing else, for auditors.

Summary

You built a two-layer security model: a `SESSION_CONTEXT()`-driven view that filters *rows* within `employees` down to one department, and a role with `GRANT`/`DENY` that controls *which objects* — the view, but explicitly not the base table — a signed-in user is even allowed to query. The `DENY` on `employees` is what turns this from a convenience into an actual guarantee: no matter what other role a demo user might later be added to, `DENY` always wins, so the raw, unfiltered table stays out of reach.