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.