Logins, Users, and the Principal Hierarchy
SQL Server security separates authentication from authorization through two distinct principal types: server-level logins and database-level users. A login (either SQL Server authentication with a username/password, or Windows/Azure AD authentication) grants access to the SQL Server instance, while a database user, mapped to that login, grants access to a specific database. This separation means a single login can be mapped to users in multiple databases, and permissions are always assigned at the user (database) level or role level, never directly to the login for database objects.
Cricket analogy: A login is like being registered as a player with the BCCI, giving you access to domestic cricket in general, while a database user is like being selected for a specific IPL franchise's squad — registration alone doesn't get you into a particular team's matches.
Roles: Fixed Server, Fixed Database, and Custom
Rather than granting permissions to individual users one at a time, SQL Server encourages grouping permissions into roles. Fixed server roles like sysadmin, dbcreator, and securityadmin operate at the instance level, while fixed database roles like db_owner, db_datareader, db_datawriter, and db_ddladmin operate within a single database. For anything beyond these built-in roles, you create custom database roles with CREATE ROLE, GRANT the exact permissions needed, and add users as members — this is the practical mechanism for implementing least privilege in real applications.
Cricket analogy: Fixed roles are like standard cricket positions — captain, wicketkeeper, opening batsman — with well-understood responsibilities, while a custom role is like a team creating a specialized 'death-over specialist' role tailored to their specific strategy.
Granting, Denying, and Revoking Permissions
Permissions in SQL Server are managed with GRANT, DENY, and REVOKE. GRANT allows an action, REVOKE removes a previously granted or denied permission (returning to an undefined state), and DENY explicitly blocks an action regardless of any GRANT the principal receives from any other role membership — DENY always wins in a conflict. Permissions can be scoped narrowly, such as SELECT on a single column or table, or broadly, such as CONTROL on an entire schema, and following least privilege means granting the narrowest scope that lets the application or user do their job.
Cricket analogy: DENY overriding GRANT is like an ICC match ban overriding any team selection — even if the captain wants a suspended player in the XI, the ban takes precedence.
-- Create a login and map it to a database user
CREATE LOGIN app_service WITH PASSWORD = 'Str0ng!Passw0rd#2026';
USE SalesDB;
CREATE USER app_service FOR LOGIN app_service;
-- Create a custom role with least-privilege permissions
CREATE ROLE app_readwrite;
GRANT SELECT, INSERT, UPDATE ON SCHEMA::dbo TO app_readwrite;
DENY DELETE ON dbo.AuditLog TO app_readwrite; -- explicit block, overrides any other GRANT
ALTER ROLE app_readwrite ADD MEMBER app_service;
-- Revoke a permission back to undefined state
REVOKE UPDATE ON dbo.PricingRules FROM app_readwrite;Never add application service accounts to db_owner or sysadmin as a shortcut to fix a permission error. This grants far more access than the app needs and turns any SQL injection vulnerability into a full database or instance compromise. Diagnose the specific missing permission and grant only that.
Use EXECUTE AS or signed stored procedures to grant elevated access for a specific operation without granting broad table-level permissions directly to users — this lets you control exactly what elevated code path a user can invoke.
- Logins authenticate at the server level; users authorize at the database level, and one login can map to users in multiple databases.
- Fixed server and database roles cover common scenarios; custom roles implement precise least-privilege access.
- GRANT allows, REVOKE clears a prior GRANT or DENY, and DENY explicitly blocks and always wins over any GRANT.
- Least privilege means scoping permissions as narrowly as possible — to a column, table, or schema — rather than defaulting to broad roles.
- Never assign application service accounts to db_owner or sysadmin as a workaround.
- EXECUTE AS and signed stored procedures allow controlled privilege elevation for specific operations.
- Windows/Azure AD authentication is generally preferred over SQL authentication for centralized credential management.
Practice what you learned
1. What is the relationship between a SQL Server login and a database user?
2. If a user is granted SELECT via a role but explicitly DENY'd SELECT on the same object directly, what happens?
3. What does REVOKE do to a previously granted permission?
4. Why is adding an application service account to db_owner considered poor practice?
5. Which mechanism lets you grant a controlled privilege elevation for a specific stored procedure without broadly granting table permissions?
Was this page helpful?
You May Also Like
Backup and Restore
How SQL Server backup types, the recovery model, and restore sequences work together to protect data and meet recovery objectives.
High Availability Basics
Core SQL Server high-availability options — Always On Availability Groups, Failover Cluster Instances, and log shipping — and how they differ.
Execution Plans and Tuning
How to read SQL Server execution plans, identify costly operators, and apply indexing and query rewrites to improve performance.
Related Reading
Related Study Notes in Programming
Browse all study notesApache Spark Study Notes
Programming · 30 topics
ProgrammingApache Flink Study Notes
Programming · 30 topics
ProgrammingHadoop Study Notes
Programming · 30 topics
ProgrammingSnowflake Study Notes
Programming · 30 topics
ProgrammingApache Airflow Study Notes
Programming · 30 topics
Programmingdbt (Data Build Tool) Study Notes
Programming · 30 topics