Sign in to save your place.

MODULE 12 OF 12

Security Basics

Logins vs users, CREATE LOGIN/USER, GRANT REVOKE DENY, and roles.

Logins vs users

A login is a server-level identity (someone who can connect to SQL Server). A user is a database-level identity—linked to a login—that has permissions inside a specific database. One login can map to one user per database.

CREATE LOGIN & CREATE USER

CREATE LOGIN creates a server login (e.g. SQL or Windows auth). CREATE USER creates a database user and associates it with a login so that user can access that database.

GRANT, REVOKE, DENY & roles

GRANT gives a permission (e.g. SELECT on a table). REVOKE removes a granted permission. DENY explicitly blocks access (overrides GRANT). Roles group permissions; add users to roles (e.g. db_datareader) to give them the role's permissions.

Key Takeaway

Login = server access. User = database access. GRANT/REVOKE/DENY control what users can do. Use roles to manage permissions in bulk.

Security layers

Server (login) → Database (user) → Securables (tables, views). Permissions (GRANT/REVOKE/DENY) apply to users or roles on securables.

CREATE LOGIN and GRANT example

-- Create a SQL login
CREATE LOGIN AppUser WITH PASSWORD = 'StrongP@ss123';

-- In the target database: create user linked to login
USE MyDB;
CREATE USER AppUser FOR LOGIN AppUser;

-- Grant SELECT on a table
GRANT SELECT ON dbo.Orders TO AppUser;

-- Revoke later if needed
REVOKE SELECT ON dbo.Orders TO AppUser;

Practice

On a dev database: CREATE LOGIN and CREATE USER, then GRANT SELECT on one table. Connect with that login and run SELECT; try UPDATE and confirm it's denied unless you grant it.

Quiz

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

Login is server-level; user is database-level and gets permissions in that database
They are the same thing
User is server-level; login is database-level
Login is for backups; user is for queries

Correct! Login = server identity; user = database identity tied to a login.

Login is at the server (can connect); user is inside a database and holds permissions there.

2. What does DENY do?

Explicitly blocks access (overrides GRANT)
Grants permission
Removes a user
Creates a backup

Correct! DENY explicitly denies permission and overrides GRANT.

DENY explicitly denies a permission; REVOKE removes a previous GRANT.

October 2026: logins, Entra, and Azure SQL

A login enters the server. A user enters one database. GRANT, REVOKE, and DENY apply to that user or to a role. Windows authentication uses a Windows account. Microsoft Entra ID, the name that replaced Azure Active Directory, is the identity service for Azure SQL. Turning on Entra in Azure does not change a laptop's Windows login by itself.

Do not commit a SQL password. Azure SQL is not the box under your desk: firewalls, contained users, and Entra are part of that service. Fabric permissions are a different product. This lesson does not set them up and does not mean you should claim Fabric.

CREATE LOGIN practice_reader WITH PASSWORD = 'replace-this-secret';
CREATE USER practice_reader FOR LOGIN practice_reader;

Try this. Create a SQL login and a user in the practice database only. Grant that user SELECT on one table. Connect as that user and confirm a second table is refused. Drop the login when you are done.

Knowledge check

What is Microsoft Entra authentication used for here?