Skip to content

Logins, Users & Permissions

1. Login vs User

Login User
Level SQL Server engine (server level) Database (database level)
Stored in master database User database
Grants access to The SQL Server instance One specific database
Permissions Server roles Database permissions (read, write, execute, …)
  • A login can be associated with multiple users (one per database)
  • A login is linked to one or more server roles

2. Login Types

Type Description
Windows login (user account) A Windows user authenticates via Active Directory
Windows login (group account) An entire Windows AD group gets access
SQL Server login Password managed by SQL Server itself
Certificate login (n/a)
Asymmetric key login (n/a)

3. User Types

Type Description
Windows user Can be a Windows user or group — connects only to this database
SQL user with password Password validated by the database — only to this database
SQL user with login Linked to a login — can connect to multiple databases
SQL user without login Has no password — can't connect, but can own objects
User mapped to a certificate Special purpose
User mapped to an asymmetric key Special purpose

4. Creating a Login

Object Explorer → Security → right-click Logins → New Login...

Choose between: - Windows authentication (look up an AD user or group) - SQL Server authentication (enter a password, set the policy)

Set: Default database, Server Roles, User Mapping.

USE [master];
GO

-- Windows-authenticated login — user account
CREATE LOGIN [DataJBE\bbibber]
FROM WINDOWS WITH DEFAULT_DATABASE = [master];
GO

-- Windows-authenticated login — local group
CREATE LOGIN [DataJBE\Developers]
FROM WINDOWS WITH DEFAULT_DATABASE = [AdventureWorks2019];
GO

-- SQL Server-authenticated login
CREATE LOGIN [JuniorDBA]
WITH PASSWORD = '-p@ssw0rd-' MUST_CHANGE,
     CHECK_EXPIRATION = ON,
     CHECK_POLICY = ON,
     DEFAULT_DATABASE = [ClassicModels];
GO

Naming guidelines for logins

  • Windows login (user): always use the fully qualified name DOMAIN\username e.g. DataJBE\jbeysen — never just the username
  • Windows login (group): likewise, DOMAIN\Groupname e.g. DataJBE\SQL_Admins
  • SQL Server login: choose a name that clearly describes the function or person e.g. AppServiceAccount, ReportingUser — avoid generic names like sa or admin
  • Never use spaces in SQL Server login names
  • For service accounts: use a dedicated account per application, not one shared account
  • Always disable the built-in sa account in production, and rename it if possible

5. Modifying a Login

Object Explorer → Security → Logins → right-click → Properties

USE [master];
GO

-- Disable a login
ALTER LOGIN [training\SeniorDeveloper] DISABLE;
GO

6. Creating a User

Object Explorer → database → Security → right-click Users → New User...

Link to an existing login and assign database roles.

USE [ClassicModels];
GO

-- Create a user linked to a login
CREATE USER [Marcel] FOR LOGIN [MarcelKiekeboe];
GO

-- Create a user with read-only permissions
ALTER ROLE db_datareader ADD MEMBER [Marcel];
GO

7. Assigning Permissions

Database Roles

Role Permissions
db_owner Full control over the database
db_datareader Read all tables
db_datawriter Read and write all tables
db_ddladmin Execute DDL statements (CREATE, ALTER, DROP)
dbcreator (server role) Create, alter, delete databases

Granular Permissions

-- Read permission on one specific table
GRANT SELECT ON Customers TO [Fanny];
GO

-- Revoke a permission
REVOKE SELECT ON Customers FROM [Fanny];
GO

-- Explicitly deny a permission
DENY SELECT ON Customers TO [Fanny];
GO

GRANT vs DENY

DENY takes precedence over GRANT. If a user has GRANT via a role but an explicit DENY, the denial applies.