LearnAI ToolsCareerPractice BuildsPlayContact
Lesson 3819 min read

Security & Permissions

Learn the difference between logins and users in SQL Server, how GRANT, DENY, and REVOKE work, and the principle of least privilege.

Introduction

SQL Server's permission system decides who can connect to the server at all, which databases they can access, and exactly what they are allowed to do inside each one - read certain tables, run certain procedures, or nothing at all. This lesson covers logins, users, roles, and the GRANT/DENY/REVOKE statements that tie them together, along with the principle of least privilege that should guide how you assign access.

What You Will Learn
  • The difference between a login and a database user.
  • How to create a login and map it to a user.
  • GRANT, DENY, and REVOKE syntax.
  • Built-in and custom database roles.
  • The principle of least privilege.

Logins vs Users

A login is a server-level identity - it lets a person or application authenticate to the SQL Server instance at all, but by itself grants no access to any particular database. A user is a database-level identity, mapped to a login, that actually holds permissions inside a specific database. One login can map to a user in each database it needs access to.

ConceptScopePurpose
LoginServer-wideAuthenticates a connection to the SQL Server instance
UserPer-databaseHolds permissions within one specific database
RolePer-database (or server-wide)A named group of permissions assigned to users

Creating a Login and User

-- Server level: create a SQL Server login with a password
CREATE LOGIN ReportingApp WITH PASSWORD = 'Str0ng!PasswordHere';


-- Database level: map that login to a user inside a specific database
USE SalesDB;
CREATE USER ReportingApp FOR LOGIN ReportingApp;
Windows Authentication

In many real-world environments, logins are Windows accounts or Active Directory groups (CREATE LOGIN [DOMAIN\\username] FROM WINDOWS) rather than SQL Server logins with their own password, since it centralizes credential management.

GRANT, DENY, and REVOKE

GRANT gives a permission, DENY explicitly blocks a permission (and always wins over a GRANT, even from a role membership), and REVOKE removes a previously granted or denied permission, returning to a neutral 'no explicit permission' state.

-- Allow ReportingApp to read from the Orders table
GRANT SELECT ON dbo.Orders TO ReportingApp;


-- Explicitly forbid ReportingApp from ever seeing the Salary column,
-- even if a role it belongs to grants broader SELECT access
DENY SELECT ON dbo.Employees (Salary) TO ReportingApp;


-- Remove a previously granted permission
REVOKE SELECT ON dbo.LegacyTable FROM ReportingApp;
DENY Always Wins

If a user is DENYed a permission directly, no GRANT - not even through a role - can override it. DENY is the strongest statement in SQL Server's permission system, which makes it useful for explicitly locking down sensitive columns or objects.

Database Roles

Instead of granting permissions to individual users one by one, you typically add users to roles and grant permissions to the role. SQL Server ships several built-in fixed database roles, and you can create your own custom roles for application-specific permission sets.

Built-in RoleGrants
db_datareaderSELECT on every table in the database
db_datawriterINSERT, UPDATE, DELETE on every table in the database
db_ownerFull control over the database
db_ddladminCan run CREATE/ALTER/DROP on database objects
-- Create a custom role and grant it specific permissions
CREATE ROLE ReportViewer;
GRANT SELECT ON dbo.vwOrderSummary TO ReportViewer;
GRANT SELECT ON dbo.vwActiveCustomers TO ReportViewer;


-- Add the user to the role instead of granting permissions individually
ALTER ROLE ReportViewer ADD MEMBER ReportingApp;

Principle of Least Privilege

The principle of least privilege means every login and user should have exactly the permissions it needs to do its job - no more. A reporting application that only ever runs SELECT statements should never be given db_owner. This limits the damage a compromised credential, a bug, or a mistaken script can cause.

Applying Least Privilege
  • Grant SELECT-only access to reporting tools instead of full read/write.
  • Use stored procedures with EXECUTE permission instead of direct table access for applications, so users cannot run arbitrary queries.
  • Avoid using db_owner or sysadmin for everyday application logins.
  • Grant access at the role level, and add/remove users from roles, rather than managing permissions per-user.

Schema-Level Permissions

Permissions can also be granted at the schema level, applying automatically to every current and future object inside it - useful when a whole application area (like a Reporting schema) should share the same access rules.

GRANT SELECT ON SCHEMA::Reporting TO ReportViewer;

Common Mistakes

Avoid These Mistakes
  • Granting db_owner or sysadmin to application logins out of convenience.
  • Managing permissions per-user instead of through roles, which becomes unmanageable as the team grows.
  • Forgetting that DENY overrides every GRANT, including ones from role membership, and using it without realizing the effect.
  • Leaving default or weak passwords on SQL Server logins.
  • Not revoking access promptly when someone changes teams or leaves the project.

Best Practices

  • Follow the principle of least privilege for every login and user.
  • Prefer Windows/Active Directory authentication and groups over individual SQL logins where possible.
  • Grant permissions to roles, not individual users, and manage membership instead.
  • Give applications access only to the specific procedures or views they need, not raw table access.
  • Periodically audit role membership and permissions, removing anything no longer needed.

Frequently Asked Questions

By default, access is denied - SQL Server requires an explicit GRANT (directly or through a role) before a permission is allowed.

Yes, but it needs a corresponding user created in each database it should access, and each of those users needs its own permissions or role memberships.

REVOKE removes a previous GRANT or DENY, returning to a neutral state with no explicit permission. DENY actively blocks the permission, overriding any GRANT even from role membership.

No - db_owner grants full control within one database, while sysadmin is a server-level role granting full control over the entire SQL Server instance, including every database.

Key Takeaways

  • A login authenticates to the server; a user, mapped to a login, holds permissions inside a database.
  • GRANT allows a permission, DENY blocks it (and always wins), REVOKE clears either back to neutral.
  • Database roles group permissions so you manage membership instead of per-user grants.
  • The principle of least privilege means granting only what is actually needed.
  • Prefer procedure/view access over direct table access for applications.

Summary

A well-designed permission model - logins, users, roles, and least-privilege grants - keeps a SQL Server database secure without getting in the way of legitimate work. Next, you will look at backup and restore, the last line of defense when something goes wrong despite every other safeguard.

Next Lesson →

Backup & Restore