Security and Permissions

SQL Server provides a comprehensive, layered security model. This lesson covers authentication, authorisation, logins, users, roles, schemas, encryption, and auditing.

---

Authentication Modes

SQL Server supports two authentication modes:

Mode	Description
Windows Authentication	Uses Windows/Active Directory credentials — no separate password needed
SQL Server Authentication	Uses a SQL Server-specific username and password
Mixed Mode	Both Windows and SQL Server authentication are enabled

Tip: Windows Authentication is more secure because it leverages Kerberos or NTLM and avoids storing passwords in connection strings.

---

Logins and Users

Server-Level: Logins

A login grants access to the SQL Server instance:

-- Windows login
CREATE LOGIN [DOMAIN\JohnSmith] FROM WINDOWS;

-- SQL Server login
CREATE LOGIN AppUser WITH PASSWORD = 'Str0ng!P@ssw0rd';

-- View all logins
SELECT name, type_desc, is_disabled FROM sys.server_principals WHERE type IN ('S', 'U');

Database-Level: Users

A user maps a login to a specific database:

USE BookStore;
CREATE USER AppUser FOR LOGIN AppUser;

-- Contained database user (no server login required)
CREATE USER ContainedUser WITH PASSWORD = 'Str0ng!P@ssw0rd';

Relationship

Server Level          Database Level
┌──────────┐         ┌──────────────┐
│  Login   │ ──────> │     User     │ ──> Tables, Views, Procedures
└──────────┘         └──────────────┘

---

Permissions

Granting Permissions

-- Grant SELECT on a specific table
GRANT SELECT ON Books TO AppUser;

-- Grant EXECUTE on a stored procedure
GRANT EXECUTE ON GetBooksByGenre TO AppUser;

-- Grant SELECT on all tables in a schema
GRANT SELECT ON SCHEMA::dbo TO AppUser;

-- Grant multiple permissions
GRANT SELECT, INSERT, UPDATE ON Books TO AppUser;

Revoking and Denying

-- Revoke a permission (removes the grant)
REVOKE SELECT ON Books FROM AppUser;

-- Deny a permission (overrides grants, even through role membership)
DENY DELETE ON Books TO AppUser;

Permission Hierarchy

Level	Examples
Server	CONNECT SQL, ALTER ANY DATABASE, VIEW SERVER STATE
Database	CREATE TABLE, ALTER ANY SCHEMA, BACKUP DATABASE
Schema	SELECT, INSERT, UPDATE, DELETE on all objects in a schema
Object	SELECT, INSERT, UPDATE, DELETE, EXECUTE on specific tables/procedures
Column	SELECT, UPDATE on specific columns

---

Roles

Fixed Server Roles

Role	Permissions
sysadmin	Full control over the entire instance
serveradmin	Server-wide configuration settings
securityadmin	Manage logins and permissions
dbcreator	Create and alter databases
bulkadmin	Run BULK INSERT statements

Fixed Database Roles

Role	Permissions
db_owner	Full control over the database
db_datareader	SELECT on all tables
db_datawriter	INSERT, UPDATE, DELETE on all tables
db_ddladmin	Run DDL statements (CREATE, ALTER, DROP)
db_securityadmin	Manage roles and permissions
db_backupoperator	Backup the database

Custom Roles

-- Create a custom role
CREATE ROLE ReportReaders;

-- Add permissions to the role
GRANT SELECT ON SCHEMA::dbo TO ReportReaders;
GRANT EXECUTE ON GetBooksByGenre TO ReportReaders;