Logins, Users, and Roles in SQL Server: The Principle of Least Privilege

Two layers, frequently conflated by beginners — getting this right is the foundation of every other security decision, including the service-account grants your Chapter 12 capstone will require you to design and justify.

Login vs User

This two-layer split has a practical consequence worth internalizing: a login can exist on the server with no matching user in a given database (meaning it can authenticate but can’t touch that database’s objects at all), and conversely a database can be moved or restored to a different server where the matching login doesn’t exist yet — producing an “orphaned user,” a genuinely common real-world migration gotcha fixed with ALTER USER ... WITH LOGIN =.

Granting Access the Right Way

CREATE LOGIN app_service WITH PASSWORD = 'Str0ng!PasswordHere#2024';
CREATE USER app_service FOR LOGIN app_service;

CREATE ROLE app_read_write;
GRANT SELECT, INSERT, UPDATE ON SCHEMA::dbo TO app_read_write;
ALTER ROLE app_read_write ADD MEMBER app_service;

Granting permissions to a role, then adding users as members, is the pattern to default to over granting permissions to individual users directly. When ten application accounts all need the same access, you manage one role’s permission set instead of ten separate, potentially-drifting grants.

DENY Always Wins

-- Even though app_read_write GRANTs UPDATE, an explicit DENY on the same table wins:
DENY UPDATE ON dbo.AuditLog TO app_read_write; -- audit logs should never be editable, even by the app

-- app_service, a member of app_read_write, now genuinely cannot UPDATE AuditLog,
-- despite the role's blanket GRANT UPDATE ON SCHEMA::dbo covering it

This makes DENY the right tool for a deliberate, hard exception to a broader grant — exactly the AuditLog scenario above, where “the app can write to most tables” needs one specific, unbreakable carve-out.

The Principle That Matters Most

An application’s service account should almost never be db_owner. Grant exactly the permissions the application actually needs — usually db_datareader + db_datawriter built-in roles, or narrower, custom roles scoped to specific tables. A compromised connection with db_owner can drop every table, read every row, and grant itself further permissions; the same compromise with a narrowly-scoped role can only do what that role permits.

Row-Level Security in SQL Server: Closing the Gap Application Code Can Miss

Lesson 1’s roles control access at the table level — “can this user query dbo.Order at all.” Row-Level Security (RLS) goes one level finer: which rows within a table a given user can see, enforced by the engine itself rather than trusted entirely to application-layer WHERE clauses. For multi-tenant applications, this closes a genuinely common, genuinely serious real-world bug class.

A Basic Security Predicate

CREATE FUNCTION dbo.fn_SecurityPredicate (@region NVARCHAR(50))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS result WHERE @region = USER_NAME() OR IS_MEMBER('db_owner') = 1;

CREATE SECURITY POLICY OrderRegionPolicy
ADD FILTER PREDICATE dbo.fn_SecurityPredicate(region) ON dbo.SomeTable
WITH (STATE = ON);

Once this policy is active, SELECT * FROM dbo.SomeTable run by a non-admin user automatically only returns rows matching their region — with no WHERE clause required in the calling code at all. The filter applies transparently to every query against the table, including ones written by developers who don’t even know RLS exists on it.

Why This Matters in Practice

This is the exact same category of reasoning as constraints vs. application-only validation (Chapter 5): trusting every single query, in every report, written by every developer who ever touches the codebase, to correctly filter by tenant is a much weaker guarantee than the engine enforcing it structurally. One forgotten WHERE tenant_id = @currentTenant in a hastily-written admin report is a real, documented category of data breach — RLS makes that specific mistake impossible rather than merely unlikely.

Auditing Existing Permissions

SELECT dp.name AS principal_name, dp.type_desc, o.permission_name, o.state_desc
FROM sys.database_permissions o
JOIN sys.database_principals dp ON dp.principal_id = o.grantee_principal_id
WHERE o.major_id = OBJECT_ID('dbo.OrderLog');

A quick, worthwhile habit before granting anything new: check what’s already granted on a sensitive table, so you’re not stacking overlapping permissions you can’t easily reason about later. Combined with Lesson 1’s DENY-always-wins rule, a table can accumulate a genuinely confusing tangle of GRANTs and DENYs across multiple roles over time — this query is how you actually see the full picture before adding to it.