SQL Server Security and Roles

The Security Model — Logins, Users, and Roles

SQL Server security is organised in two distinct layers. The first is the server level, where a login controls who can connect to the SQL Server instance. The second is the database level, where a user — mapped from a login — controls what that person can do inside a specific database. A login without a database user can connect to the server but cannot access any database. A database user exists inside one database and inherits permissions from the roles it belongs to or from explicit grants.

A role is a named collection of permissions. Rather than granting individual permissions to each user, you assign users to roles and manage permissions at the role level. SQL Server ships with a set of fixed server roles and fixed database roles covering common needs. You can also create custom database roles to match your application's exact access requirements.

• A login is a server-level principal — it controls connection access to the SQL Server instance
• A database user is a database-level principal — it controls access within a single database
• Every database user must be mapped to a login (or be a special user like a contained user)
• A role groups permissions together — assign users to roles rather than granting permissions one by one
• Fixed server roles (e.g. sysadmin, securityadmin) control server-wide operations
• Fixed database roles (e.g. db_owner, db_datareader, db_datawriter) control per-database access

-- Step 1: create a server login (SQL Server authentication)
CREATE LOGIN app_readonly
WITH PASSWORD = 'Str0ng!Passw0rd',
     CHECK_POLICY = ON,          -- enforce Windows password policy
     CHECK_EXPIRATION = ON;      -- enforce password expiration
-- Step 2: inside the target database, create a user mapped to that login
USE DataplexaStore;
CREATE USER app_readonly FOR LOGIN app_readonly;
-- Step 3: assign the user to a fixed database role
-- db_datareader: SELECT on all tables and views in the database
ALTER ROLE db_datareader ADD MEMBER app_readonly;
-- Verify the role membership
SELECT
    r.name  AS RoleName,
    m.name  AS MemberName
FROM sys.database_role_members drm
JOIN sys.database_principals   r ON drm.role_principal_id   = r.principal_id
JOIN sys.database_principals   m ON drm.member_principal_id = m.principal_id
WHERE m.name = 'app_readonly';

• The login lives at the server level — it authenticates the connection
• The user lives inside DataplexaStore — it carries the database permissions
• db_datareader grants SELECT on every table and view in the database without needing per-table grants
• CHECK_POLICY = ON enforces the Windows password complexity rules — always enable this for SQL authentication logins in production

Fixed Database Roles

SQL Server's built-in database roles cover the most common permission patterns. Knowing which role to assign prevents both under-permission (users cannot do their job) and over-permission (users can do things they should not). The most important fixed database roles for day-to-day administration are listed here — understanding them lets you make the right choice without creating custom roles for simple cases.

-- Fixed database roles and what each one grants.
-- Query sys.database_principals to see all roles in the current database.
SELECT
    name        AS RoleName,
    type_desc   AS PrincipalType
FROM sys.database_principals
WHERE type = 'R'             -- R = database role
ORDER BY name;
-- Common fixed roles and their purpose:
-- db_owner        : full control -- can perform any operation in the database
-- db_securityadmin: manage role memberships and object permissions
-- db_accessadmin  : add or remove database users
-- db_datareader   : SELECT on all tables and views
-- db_datawriter   : INSERT, UPDATE, DELETE on all tables
-- db_ddladmin     : CREATE, ALTER, DROP -- no data read/write
-- db_denydatareader: explicitly DENY SELECT on all tables
-- db_denydatawriter: explicitly DENY INSERT/UPDATE/DELETE on all tables
-- Assign a user to multiple roles for combined access:
-- read + write but no schema changes
ALTER ROLE db_datareader ADD MEMBER reporting_user;
ALTER ROLE db_datawriter ADD MEMBER reporting_user;

• db_owner is the most powerful database role — assign it only to DBAs who genuinely need full control
• Combining db_datareader and db_datawriter gives full read and write access without schema-change rights — the right choice for most application accounts
• db_denydatareader takes precedence over db_datareader — DENY always wins over GRANT in SQL Server's permission model
• The public role is a special role that every database user belongs to automatically — avoid granting permissions to public

GRANT, DENY, and REVOKE

When fixed roles are too broad, use GRANT, DENY, and REVOKE to control permissions at the object level — individual tables, views, stored procedures, or columns. GRANT allows. DENY explicitly forbids — it overrides any GRANT from any role the user belongs to. REVOKE removes a previously granted or denied permission, returning the effective permission to whatever the user's roles provide. This three-way model gives precise control when role-level access does not match the requirement.

-- GRANT: give a specific permission on a specific object
GRANT SELECT ON dbo.Products  TO app_readonly;    -- allow SELECT on Products only
GRANT SELECT ON dbo.Orders    TO app_readonly;    -- allow SELECT on Orders only
GRANT EXECUTE ON dbo.GetCustomerOrders TO app_readonly;  -- allow calling a TVF
-- GRANT column-level permission: only specific columns
GRANT SELECT ON dbo.Users (UserId, FirstName, LastName, Country)
    TO app_readonly;     -- cannot SELECT Email or DateOfBirth
-- DENY: explicitly forbid -- overrides any GRANT from roles
DENY SELECT ON dbo.UserProfiles TO app_readonly;  -- cannot see profile data at all
-- REVOKE: remove a previously issued GRANT or DENY
REVOKE SELECT ON dbo.Products FROM app_readonly;  -- permission returns to role default
-- Check effective permissions for a user on a specific table
SELECT
    permission_name,
    state_desc          AS PermissionState   -- GRANT, DENY, or REVOKE
FROM sys.database_permissions
WHERE major_id  = OBJECT_ID('dbo.Orders')
  AND grantee_principal_id = USER_ID('app_readonly');

• Column-level GRANT restricts access to only the listed columns — the user cannot SELECT any other column from that table
• DENY on UserProfiles overrides the db_datareader role grant — even though the role allows reading all tables, the explicit DENY wins
• REVOKE on Products removes the explicit grant — access now falls back to whatever the user's roles allow
• Use sys.database_permissions filtered by OBJECT_ID and USER_ID to audit the exact permissions on any object for any user

Custom Database Roles

When fixed roles are either too permissive or too restrictive, create a custom database role that grants exactly the permissions your application needs. Custom roles follow the same GRANT/DENY/REVOKE model as individual users — once the role is configured, adding a user to it instantly gives them its full permission set. This is the recommended pattern for application accounts: create one role per application function and assign logins to those roles rather than granting permissions directly to users.

-- Create a custom role for the DataplexaStore reporting application.
-- Needs: SELECT on orders/products, EXECUTE on reporting procedures, no writes.
CREATE ROLE role_reporting;
-- Grant the exact permissions this role needs
GRANT SELECT  ON dbo.Orders      TO role_reporting;
GRANT SELECT  ON dbo.OrderItems  TO role_reporting;
GRANT SELECT  ON dbo.Products    TO role_reporting;
GRANT SELECT  ON dbo.Users       TO role_reporting;
GRANT EXECUTE ON dbo.GetCustomerOrders TO role_reporting;
GRANT EXECUTE ON dbo.GetCustomerTiers  TO role_reporting;
-- Deny access to sensitive tables
DENY  SELECT  ON dbo.UserProfiles TO role_reporting;
-- Add users to the custom role
ALTER ROLE role_reporting ADD MEMBER app_readonly;
ALTER ROLE role_reporting ADD MEMBER reporting_svc;
-- Remove a user from a role
ALTER ROLE role_reporting DROP MEMBER app_readonly;
-- Drop the role (all members must be removed first)
-- ALTER ROLE role_reporting DROP MEMBER reporting_svc;
-- DROP ROLE role_reporting;

• Custom roles express intent clearly — role_reporting immediately communicates what this set of permissions is for
• Adding a new reporting user requires one line: ALTER ROLE role_reporting ADD MEMBER new_user — all permissions are inherited instantly
• A role cannot be dropped while it has members — remove all members first
• Prefer granting permissions to roles over granting them directly to users — direct user grants scatter across the schema and become unmanageable

Auditing Logins, Users, and Permissions

SQL Server's system catalogue provides a complete picture of every login, user, role membership, and permission in the instance. Querying these views regularly — or as part of a security review — ensures that access rights match what was intended and that old accounts from departed employees or decommissioned applications have been removed. Three views cover most auditing needs: sys.server_principals for logins, sys.database_principals for users and roles, and sys.database_permissions for object-level grants and denies.

-- Full permission audit: who has access to what in DataplexaStore?
-- 1. All logins on the server
SELECT name AS LoginName, type_desc, is_disabled, create_date
FROM sys.server_principals
WHERE type IN ('S', 'U', 'G')   -- S=SQL login, U=Windows user, G=Windows group
ORDER BY name;
-- 2. All users and their mapped logins in the current database
SELECT
    dp.name         AS DatabaseUser,
    dp.type_desc    AS UserType,
    sp.name         AS MappedLogin,
    dp.create_date
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE dp.type IN ('S', 'U')     -- exclude roles and special users
ORDER BY dp.name;
-- 3. All explicit object-level permissions in the current database
SELECT
    USER_NAME(p.grantee_principal_id)  AS Grantee,
    OBJECT_NAME(p.major_id)            AS ObjectName,
    p.permission_name,
    p.state_desc                       AS PermissionState
FROM sys.database_permissions p
WHERE p.class = 1               -- class 1 = object-level permissions
  AND p.major_id > 0            -- exclude database-scoped permissions
ORDER BY Grantee, ObjectName;

• The sa login is disabled (is_disabled = 1) — this is correct best practice; the sa account should always be disabled in production
• The three queries together give a complete picture: who can connect, what database users exist, and what each user or role can do to each object
• Run this audit after any access change and before any security review — it is the fastest way to verify the permission model matches the design
• Schedule these queries as a regular job to detect unauthorised permission changes or forgotten accounts from old projects

Lesson Summary

Practice Questions

Quiz

Next up — Backup & Restore — How to protect your database with full, differential, and transaction log backups and recover from failure.