Skip to content
Tutorials Center
My learning

Topics

Lesson 33 of 39 ยท Administration and Security

Logins, Users, Roles and Permissions

Learn the SQL Server security model: logins versus database users, fixed and custom roles, GRANT, DENY and REVOKE, schemas, ownership chaining and least-privilege design.

SQL Server's security has two layers, and confusing them is the most common beginner mistake. A login lets you connect to the server. A user lets that login work inside one database. Permissions are then granted to users, usually through roles. Getting this right means the application has access to exactly what it needs, and nothing else: the principle of least privilege.

Security statements can't run in the browser, so the T-SQL is read-only, and a runnable stand-in models a role and permission matrix with plain tables. See also users and permissions for the portable ideas.

Authentication modes

  • Windows authentication (recommended): SQL Server trusts Active Directory or the Windows account. There is no password to store.
  • SQL Server authentication: the server keeps a login name and password hash. Needed for non-Windows clients and some apps. Enforce CHECK_POLICY (Windows password rules).
  • Mixed mode allows both. In Azure SQL, Microsoft Entra ID (Azure AD) authentication plays the Windows role.

The built-in sa login is a permanent target for attacks: disable it or rename it, and use a long password if you keep it.

Logins and users

SQL
-- Server level: who may connect
CREATE LOGIN ShopApp WITH PASSWORD = N'long-random-secret-here', CHECK_POLICY = ON;
CREATE LOGIN [CONTOSO\ana] FROM WINDOWS;

-- Database level: map the login to a user inside the database
USE Shop;
CREATE USER ShopApp FOR LOGIN ShopApp;

If a user is not mapped to a login, it is orphaned; that happens after restoring a database on another server. ALTER USER ShopApp WITH LOGIN = ShopApp; repairs it. Contained databases avoid the problem with CREATE USER ... WITH PASSWORD, so the database carries its own users.

Every database also has the special users dbo (the owner, who can do anything) and guest, which should stay disabled.

Roles

A role groups permissions. You add users to roles instead of granting rights one by one.

Server roles (fixed): sysadmin (everything: keep it nearly empty), securityadmin, serveradmin, dbcreator, processadmin, bulkadmin, diskadmin, setupadmin, and, from SQL Server 2022, finer ones such as ##MS_ServerStateReader##.

Database roles (fixed): db_owner, db_datareader (SELECT on everything), db_datawriter, db_ddladmin, db_securityadmin, db_backupoperator, db_denydatareader, db_denydatawriter.

Prefer your own roles that match jobs:

SQL
CREATE ROLE SalesReader;
GRANT SELECT ON SCHEMA::Sales TO SalesReader;

CREATE ROLE OrderWriter;
GRANT EXECUTE ON dbo.AddOrder TO OrderWriter;
GRANT EXECUTE ON dbo.CancelOrder TO OrderWriter;

ALTER ROLE SalesReader ADD MEMBER ShopApp;

GRANT, DENY and REVOKE

Statement Effect
GRANT Gives a permission
DENY Explicitly forbids it; wins over any GRANT
REVOKE Removes a previous GRANT or DENY (back to neutral)
SQL
GRANT SELECT, INSERT ON dbo.Orders TO ShopApp;
DENY  SELECT ON dbo.Customers (CreditCardNo) TO ShopApp;   -- column-level
REVOKE INSERT ON dbo.Orders FROM ShopApp;
GRANT UPDATE ON dbo.Products TO SalesManager WITH GRANT OPTION;  -- may pass it on

Permissions can be set on a database, a schema, a table, a column or a procedure. They are additive across roles: a user gets the union of what all their roles grant, minus anything denied. That's why a single DENY is a strong, and slightly dangerous, tool. To check what a user can do, impersonate and test:

SQL
EXECUTE AS USER = 'ShopApp';
SELECT * FROM fn_my_permissions('dbo.Orders', 'OBJECT');
REVERT;

Schemas as a security boundary

A schema is a namespace inside a database (Sales.Orders, HR.Salaries). Granting on a schema covers everything in it, now and in the future, which makes it a natural unit for permissions. Keep application tables out of dbo when you need clean separation.

Ownership chaining and procedures

If a stored procedure and the tables it uses have the same owner, SQL Server checks permission only on the procedure when someone runs it. The user needs EXECUTE, not SELECT on the tables. This lets you give the application no direct table access, so even a stolen app password can't run SELECT * FROM Customers. It stops working when the procedure uses dynamic SQL, because dynamic statements are checked against the caller. EXECUTE AS OWNER or module signing fix that.

A stand-in: a permission matrix

Models are the best way to see how roles and denies combine. Here a user's effective access is computed from role grants minus denies:

SQL Effective permissions: GRANT unions, DENY wins

Press Run (or Ctrl + Enter) to see the result here.

ShopApp loses SELECT on Customers because of the DENY, even though a role grants it. Ana keeps both reads.

SQL Find users who can do more than they should

Press Run (or Ctrl + Enter) to see the result here.

A real audit queries sys.server_role_members and sys.database_role_members for the same thing.

Security checklist

  1. Windows or Entra ID authentication wherever possible.
  2. One login per application, never sa and never sysadmin/db_owner for an app.
  3. Roles per job; grant to roles, not to people.
  4. Grant on schemas and procedures; deny direct table access to applications when you can.
  5. Review membership of powerful roles regularly. Next up: encryption, masking and auditing.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the difference between a login and a user?

    Logins live at the server level; users are database-level principals mapped to logins.
  2. 2. A user is in a role that has GRANT SELECT on a table, and has an individual DENY SELECT on it. What happens?

    DENY always wins over GRANT, no matter which route the grant came through.
  3. 3. What does REVOKE do?

    REVOKE is neutral, it erases the entry rather than forbidding anything.
  4. 4. Why might an application only need EXECUTE on procedures and no table permissions?

    When the procedure and tables share an owner, permissions are checked on the procedure only.

Summary

  • Logins connect to the server; users and roles control access in each database.
  • Use custom roles, schema-level grants and procedures; DENY beats GRANT.
  • Keep sysadmin and db_owner membership tiny, and never use them for applications.
  • Ownership chaining lets procedures expose data safely; dynamic SQL breaks the chain.

Next: Encryption, masking and auditing