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