15 — Principals & Permissions

SQL Server Instance
├── Server Principals  (stored in master, surfaced in sys.server_principals)
│   ├── Logins (SQL Login, Windows Login, Windows Group, Certificate, Asymmetric Key)
│   └── Server Roles (fixed + user-defined since 2012)
└── Database
    ├── Database Principals  (per-database, surfaced in sys.database_principals)
    │   ├── Users (mapped to login, contained, certificate, no-login, Windows)
    │   ├── Database Roles (fixed + custom)
    │   └── Application Roles
    └── Securables
        ├── Server-scope  (logins, endpoints, server objects)
        ├── Database-scope (schemas, certificates, assemblies, full-text catalogs)
        └── Schema-scope  (tables, views, procs, functions, types, …)

-- All enabled SQL logins
SELECT name, type_desc, default_database_name, create_date
FROM sys.server_principals
WHERE type IN ('S','U','G')
  AND is_disabled = 0
ORDER BY name;

-- Members of a fixed server role
SELECT sp.name AS member, spr.name AS role
FROM sys.server_role_members srm
JOIN sys.server_principals sp  ON sp.principal_id  = srm.member_principal_id
JOIN sys.server_principals spr ON spr.principal_id = srm.role_principal_id
ORDER BY spr.name, sp.name;

-- All non-system users in current database with their mapped login
SELECT
    dp.name         AS db_user,
    dp.type_desc,
    dp.default_schema_name,
    sp.name         AS login_name,
    sp.type_desc    AS login_type,
    sp.is_disabled  AS login_disabled
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON sp.sid = dp.sid
WHERE dp.type NOT IN ('R','A')  -- exclude roles and app roles
  AND dp.principal_id > 4        -- exclude system principals
ORDER BY dp.name;

-- Find orphaned users (no matching login)
SELECT name AS orphaned_user
FROM sys.database_principals
WHERE type IN ('S','U','G')
  AND authentication_type = 1  -- instance-authenticated
  AND sid NOT IN (
      SELECT sid FROM sys.server_principals
  )
  AND principal_id > 4;

-- Fix: remap an orphaned user to its login
ALTER USER [myuser] WITH LOGIN = [mylogin];
-- Or drop and recreate:
DROP USER [myuser];
CREATE USER [myuser] FOR LOGIN [mylogin];

-- Create SQL login
CREATE LOGIN myapp_login
    WITH PASSWORD = 'Str0ng!Pass#',
         DEFAULT_DATABASE = MyDB,
         CHECK_POLICY = ON,      -- enforces Windows password policy
         CHECK_EXPIRATION = ON;  -- enforces password expiration

-- Disable / enable
ALTER LOGIN myapp_login DISABLE;
ALTER LOGIN myapp_login ENABLE;

-- Change password
ALTER LOGIN myapp_login WITH PASSWORD = 'New!Pass#'
    OLD_PASSWORD = 'Str0ng!Pass#';  -- non-sysadmin must supply old password

-- Unlock after failed attempts
ALTER LOGIN myapp_login WITH PASSWORD = 'Str0ng!Pass#' UNLOCK;

-- Drop
DROP LOGIN myapp_login;

CREATE LOGIN [DOMAIN\myuser]    FROM WINDOWS WITH DEFAULT_DATABASE = MyDB;
CREATE LOGIN [DOMAIN\MyAppGrp] FROM WINDOWS;

-- Windows logins authenticate via Kerberos/NTLM; CHECK_POLICY does not apply

-- Used for ownership chaining across databases or for signed modules
CREATE CERTIFICATE cert_for_proc
    ENCRYPTION BY PASSWORD = 'CertPass!'
    WITH SUBJECT = 'Module signing cert',
         EXPIRY_DATE = '2030-01-01';

CREATE LOGIN cert_login FROM CERTIFICATE cert_for_proc;

-- Standard: mapped to a login
USE MyDB;
CREATE USER myapp_user FOR LOGIN myapp_login
    WITH DEFAULT_SCHEMA = app;

-- No-login user (for module signing, cannot connect)
CREATE USER signing_user WITHOUT LOGIN;

-- Contained database user (authenticates at DB level, no server login needed)
CREATE USER contained_user
    WITH PASSWORD = 'Str0ng!Pass#',
         DEFAULT_SCHEMA = dbo;

-- Windows user directly in database
CREATE USER [DOMAIN\myuser] FOR LOGIN [DOMAIN\myuser];

-- Guest account (disabled by default in new DBs — keep it disabled)
-- REVOKE CONNECT FROM guest;  -- removes CONNECT permission, effectively disabling

-- Enable partial containment
ALTER DATABASE MyDB SET CONTAINMENT = PARTIAL;

-- Add login to server role
ALTER SERVER ROLE sysadmin ADD MEMBER mylogin;

-- Remove
ALTER SERVER ROLE sysadmin DROP MEMBER mylogin;

-- User-defined server roles (2012+)
CREATE SERVER ROLE readonly_servers;
GRANT VIEW ANY DATABASE TO readonly_servers;
ALTER SERVER ROLE readonly_servers ADD MEMBER mylogin;

-- Add user to role
ALTER ROLE db_datareader ADD MEMBER myapp_user;

-- Remove
ALTER ROLE db_datareader DROP MEMBER myapp_user;

-- View role membership
SELECT dp.name AS member, r.name AS role
FROM sys.database_role_members drm
JOIN sys.database_principals dp ON dp.principal_id = drm.member_principal_id
JOIN sys.database_principals r  ON r.principal_id  = drm.role_principal_id
ORDER BY r.name, dp.name;

-- Create role
CREATE ROLE app_reader;
CREATE ROLE app_writer;
CREATE ROLE app_exec;

-- Grant permissions to role
GRANT SELECT ON SCHEMA::app TO app_reader;
GRANT INSERT, UPDATE, DELETE ON SCHEMA::app TO app_writer;
GRANT EXECUTE ON SCHEMA::app TO app_exec;

-- Assign users to roles
ALTER ROLE app_reader ADD MEMBER user1;
ALTER ROLE app_writer ADD MEMBER user2;
ALTER ROLE app_exec   ADD MEMBER user1;
ALTER ROLE app_exec   ADD MEMBER user2;

-- Effective permissions query
SELECT
    dp.name         AS principal,
    dp.type_desc,
    o.name          AS object_name,
    o.type_desc     AS object_type,
    p.permission_name,
    p.state_desc
FROM sys.database_permissions p
JOIN sys.database_principals dp ON dp.principal_id = p.grantee_principal_id
LEFT JOIN sys.objects o          ON o.object_id     = p.major_id
WHERE dp.name = 'app_reader'
ORDER BY o.name, p.permission_name;

-- Create
CREATE APPLICATION ROLE myapp_role
    WITH PASSWORD = 'AppRole!Pass#',
         DEFAULT_SCHEMA = dbo;

GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::app TO myapp_role;

-- Activate from application (T-SQL)
EXEC sp_setapprole 'myapp_role', 'AppRole!Pass#';
-- After this call the session has ONLY myapp_role permissions — the original user context is gone

-- Deactivate using cookie (save cookie before activation)
DECLARE @cookie varbinary(8000);
EXEC sp_setapprole 'myapp_role', 'AppRole!Pass#',
    @fCreateCookie = true,
    @cookie = @cookie OUTPUT;

-- … do work …

EXEC sp_unsetapprole @cookie;
-- Session reverts to original user context

-- Grant SELECT on a table
GRANT SELECT ON dbo.Orders TO myapp_user;

-- Grant EXECUTE on a procedure
GRANT EXECUTE ON dbo.usp_GetOrders TO app_exec;

-- Grant at schema level (applies to all objects in schema, now and future)
GRANT SELECT ON SCHEMA::reporting TO app_reader;

-- Grant database-scope permission
GRANT VIEW DATABASE STATE TO monitoring_user;

-- Grant server-scope permission
GRANT VIEW SERVER STATE TO monitoring_login;

-- Deny overrides grant — this user cannot SELECT even if in a role with GRANT
DENY SELECT ON dbo.SensitiveTable TO restricted_user;

-- Revoke removes a previous GRANT or DENY
REVOKE SELECT ON dbo.Orders FROM myapp_user;

-- WITH GRANT OPTION: lets the grantee re-grant to others
GRANT SELECT ON dbo.Orders TO power_user WITH GRANT OPTION;

-- Revoke the grant option only (does not revoke SELECT itself)
REVOKE GRANT OPTION FOR SELECT ON dbo.Orders FROM power_user CASCADE;

CONTROL SERVER
    └── ALTER ANY DATABASE
        └── CONTROL DATABASE  (db_owner implied)
            └── CONTROL SCHEMA
                └── SELECT on Table

-- Check effective permissions for current user
SELECT * FROM fn_my_permissions(NULL, 'DATABASE');
SELECT * FROM fn_my_permissions('dbo.Orders', 'OBJECT');

-- Check effective permissions for another principal
EXECUTE AS USER = 'myapp_user';
SELECT * FROM fn_my_permissions('dbo.Orders', 'OBJECT');
REVERT;

-- Full permission graph for a user (including via roles)
WITH perms AS (
    SELECT
        dp.name         AS principal,
        p.class_desc,
        OBJECT_NAME(p.major_id) AS object_name,
        p.permission_name,
        p.state_desc
    FROM sys.database_permissions p
    JOIN sys.database_principals dp ON dp.principal_id = p.grantee_principal_id
    WHERE dp.name = 'myapp_user'
    UNION ALL
    -- Permissions via role membership
    SELECT
        dp.name,
        p.class_desc,
        OBJECT_NAME(p.major_id),
        p.permission_name,
        p.state_desc
    FROM sys.database_permissions p
    JOIN sys.database_principals role_dp ON role_dp.principal_id = p.grantee_principal_id
    JOIN sys.database_role_members drm   ON drm.role_principal_id = role_dp.principal_id
    JOIN sys.database_principals dp      ON dp.principal_id       = drm.member_principal_id
    WHERE dp.name = 'myapp_user'
)
SELECT DISTINCT * FROM perms
ORDER BY class_desc, object_name, permission_name;

-- Setup: both proc and table owned by dbo
CREATE TABLE dbo.SensitiveData (id int, secret nvarchar(100));

CREATE PROCEDURE dbo.usp_GetSensitiveData
AS
    SELECT id, secret FROM dbo.SensitiveData;  -- same owner = chain is unbroken
GO

-- Grant EXECUTE on proc only; no SELECT on table needed
GRANT EXECUTE ON dbo.usp_GetSensitiveData TO limited_user;
-- limited_user can call the proc and read the data despite no direct SELECT grant

-- Broken chain example
CREATE TABLE user_b.PrivateTable (id int);  -- owned by user_b
CREATE PROCEDURE dbo.usp_BrokenChain
AS
    SELECT * FROM user_b.PrivateTable;  -- SQL Server checks caller's permissions here
GO
-- limited_user needs explicit SELECT on user_b.PrivateTable

CREATE SCHEMA app AUTHORIZATION dbo;

USE TargetDB;
CREATE USER myapp_user FOR LOGIN myapp_login;
GRANT SELECT ON SCHEMA::dbo TO myapp_user;

ALTER DATABASE SourceDB  SET DB_CHAINING ON;
ALTER DATABASE TargetDB  SET DB_CHAINING ON;

-- In SourceDB: sign the procedure
ADD SIGNATURE TO dbo.usp_CrossDbProc
    BY CERTIFICATE cert_for_proc WITH PASSWORD = 'CertPass!';

-- In TargetDB: create user from same cert (copy cert without private key)
CREATE CERTIFICATE cert_for_proc
    FROM BINARY = <same_cert_binary>;  -- export/import without private key
CREATE USER cert_user FROM CERTIFICATE cert_for_proc;
GRANT SELECT ON dbo.TargetTable TO cert_user;

ALTER DATABASE MyDB SET TRUSTWORTHY ON;

-- Check which databases have TRUSTWORTHY on
SELECT name, is_trustworthy_on FROM sys.databases WHERE is_trustworthy_on = 1;

-- Switch to a named user
EXECUTE AS USER = 'myapp_user';
SELECT CURRENT_USER;   -- myapp_user
SELECT SYSTEM_USER;    -- still the original login

-- Revert to original context
REVERT;

-- Switch to login context
EXECUTE AS LOGIN = 'mylogin';
REVERT;

CREATE PROCEDURE dbo.usp_AuditedAction
    WITH EXECUTE AS OWNER   -- proc runs with owner's permissions, not caller's
AS
    INSERT INTO dbo.AuditLog (action, performed_by, ts)
    VALUES ('SomeAction', ORIGINAL_LOGIN(), SYSDATETIME());
GO

-- Options:
-- EXECUTE AS CALLER (default) — caller's permissions apply
-- EXECUTE AS OWNER            — proc owner's permissions apply; ownership chaining
-- EXECUTE AS SELF             — creator's permissions at create time (risky for dynamic SQL)
-- EXECUTE AS 'specific_user'  — specific named user's permissions

-- Practical use: guard a procedure against unauthorized callers
IF HAS_PERMS_BY_NAME('dbo.SensitiveTable', 'OBJECT', 'SELECT') = 0
    THROW 50001, 'Caller does not have SELECT on SensitiveTable.', 1;

-- Create schemas with dbo ownership
CREATE SCHEMA app    AUTHORIZATION dbo;
CREATE SCHEMA rpt    AUTHORIZATION dbo;
CREATE SCHEMA etl    AUTHORIZATION dbo;
CREATE SCHEMA audit_ AUTHORIZATION dbo;

-- Grant read on reporting schema
GRANT SELECT ON SCHEMA::rpt TO app_reader;

-- Grant write on app schema
GRANT INSERT, UPDATE, DELETE ON SCHEMA::app TO app_writer;

-- Grant execute on app schema (all procs/functions)
GRANT EXECUTE ON SCHEMA::app TO app_exec;

-- VIEW DEFINITION: allows seeing proc/view source without execute rights
GRANT VIEW DEFINITION ON SCHEMA::app TO developer_role;

SELECT
    sp.name          AS login_name,
    sp.type_desc,
    sp.is_disabled,
    STRING_AGG(r.name, ', ') WITHIN GROUP (ORDER BY r.name) AS server_roles
FROM sys.server_principals sp
LEFT JOIN sys.server_role_members srm ON srm.member_principal_id = sp.principal_id
LEFT JOIN sys.server_principals r     ON r.principal_id          = srm.role_principal_id
WHERE sp.type IN ('S','U','G')
GROUP BY sp.name, sp.type_desc, sp.is_disabled
ORDER BY sp.name;

SELECT
    dp.name          AS db_user,
    dp.type_desc,
    dp.default_schema_name,
    STRING_AGG(r.name, ', ') WITHIN GROUP (ORDER BY r.name) AS db_roles
FROM sys.database_principals dp
LEFT JOIN sys.database_role_members drm ON drm.member_principal_id = dp.principal_id
LEFT JOIN sys.database_principals r     ON r.principal_id          = drm.role_principal_id
WHERE dp.type NOT IN ('R','A')
  AND dp.principal_id > 4
GROUP BY dp.name, dp.type_desc, dp.default_schema_name
ORDER BY dp.name;

SELECT
    pr.name             AS principal,
    pr.type_desc        AS principal_type,
    p.class_desc        AS securable_class,
    COALESCE(
        OBJECT_SCHEMA_NAME(p.major_id) + '.' + OBJECT_NAME(p.major_id),
        SCHEMA_NAME(p.major_id),
        CAST(p.major_id AS varchar(20))
    )                   AS securable_name,
    p.permission_name,
    p.state_desc
FROM sys.database_permissions p
JOIN sys.database_principals  pr ON pr.principal_id = p.grantee_principal_id
WHERE pr.principal_id > 4
ORDER BY pr.name, p.class_desc, securable_name, p.permission_name;

SELECT
    d.name,
    d.is_trustworthy_on,
    sp.name AS owner_login
FROM sys.databases d
JOIN sys.server_principals sp ON sp.sid = d.owner_sid
WHERE d.is_trustworthy_on = 1
  AND sp.name != 'sa';

SELECT dp.name AS orphaned_user, dp.type_desc
FROM sys.database_principals dp
WHERE dp.type IN ('S','U')
  AND dp.authentication_type = 1  -- instance auth
  AND NOT EXISTS (
      SELECT 1 FROM sys.server_principals sp
      WHERE sp.sid = dp.sid
  )
  AND dp.principal_id > 4;

-- Quick check: recent permission changes using default trace (SQL Server 2022 still supports this)
SELECT
    te.name         AS event_name,
    t.StartTime,
    t.NTUserName,
    t.ApplicationName,
    t.HostName,
    t.LoginName,
    t.DatabaseName,
    t.ObjectName,
    t.TextData
FROM sys.fn_trace_gettable(
    (SELECT path FROM sys.traces WHERE is_default = 1), DEFAULT
) t
JOIN sys.trace_events te ON te.trace_event_id = t.EventClass
WHERE te.name IN (
    'Audit Add Member to DB Role Event',
    'Audit Add Login to Server Role Event',
    'Audit Schema Object GDR Event',   -- GRANT/DENY/REVOKE on objects
    'Audit Database Principal Management'
)
ORDER BY t.StartTime DESC;