Permissions, Auditing, and Passwordless Access

Who can do what — and who is watching

Object-level permissions

SQL Server uses a hierarchy: server → database → schema → object. Permissions flow down and DENY always wins.

-- Grant SELECT on specific tables
GRANT SELECT ON SCHEMA::Sales TO [SalesTeam];

-- Grant EXECUTE on a stored procedure
GRANT EXECUTE ON dbo.usp_GetCustomerOrders TO [AppService];

-- Deny direct table access but allow through procedures
DENY SELECT ON dbo.Customers TO [AppService];
GRANT EXECUTE ON dbo.usp_GetCustomer TO [AppService];
-- AppService can only access customer data through the stored procedure

Principle of least privilege

Pattern	Example	Why
Schema-level grants	GRANT SELECT ON SCHEMA::Reports	Simpler than per-table grants
Procedure-only access	DENY on tables, GRANT on procedures	Application sees only what procedures expose
Database roles	CREATE ROLE ReadOnlyAnalyst; GRANT SELECT TO ReadOnlyAnalyst	Group permissions, assign users to roles
EXECUTE AS	CREATE PROCEDURE ... WITH EXECUTE AS 'AppUser'	Procedure runs with specific identity regardless of caller

Passwordless access

Modern SQL authentication eliminates passwords entirely using Microsoft Entra ID (formerly Azure AD).

Authentication methods — passwordless is always preferred

Method	How It Works	Best For
Managed Identity	Azure resource authenticates automatically — no credentials stored	Azure VMs, App Service, Functions connecting to Azure SQL
Service Principal	Application registers in Entra ID, authenticates with certificate or federated credential	Non-Azure apps, CI/CD pipelines
Entra ID user	Interactive login with MFA, SSO	Developers, DBAs, analysts
SQL authentication	Username + password stored in connection string	Legacy only — avoid for new applications

-- Create a contained database user for a managed identity
CREATE USER [my-app-service] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [my-app-service];
ALTER ROLE db_datawriter ADD MEMBER [my-app-service];

SQL auditing

Auditing records database events to a log for compliance and security investigation.

Azure SQL auditing

-- Server-level auditing (Azure SQL) — configured in Azure portal or CLI
-- Logs to Azure Blob Storage, Log Analytics, or Event Hub

-- Database-level audit specifications
CREATE DATABASE AUDIT SPECIFICATION AuditSensitiveAccess
FOR SERVER AUDIT [AzureSQLAudit]
ADD (SELECT, INSERT, UPDATE, DELETE ON dbo.Customers BY [public]),
ADD (EXECUTE ON dbo.usp_TransferFunds BY [public])
WITH (STATE = ON);

💡 Exam tip: Auditing is not optional for compliance

The exam often presents scenarios where a company must demonstrate “who accessed what data and when” for regulatory compliance (GDPR, SOX, HIPAA). The answer is always auditing — not just permissions. Permissions control access; auditing proves what actually happened.

For Azure SQL, audit logs can flow to: Blob Storage (cheapest), Log Analytics (queryable with KQL), or Event Hub (real-time streaming to SIEM tools).

Question

What is the advantage of passwordless authentication over SQL authentication?

Click or press Enter to reveal answer

Answer

Passwordless (managed identity, Entra ID) eliminates credential management — no passwords to rotate, leak, or store in connection strings. Managed identity authenticates automatically with no secrets. SQL authentication requires passwords that can be stolen or exposed.

Click to flip back

Question

What does DENY always do in the permission hierarchy?

Click or press Enter to reveal answer

Answer

DENY always wins, regardless of any GRANT. If a user is granted SELECT through a role but explicitly DENIED SELECT, the DENY takes precedence. This makes DENY a powerful but dangerous tool — use it sparingly.

Click to flip back

Question

What is the difference between server audit and database audit specification?

Click or press Enter to reveal answer

Answer

A server audit defines WHERE audit data goes (Blob Storage, file, Event Hub). A database audit specification defines WHAT events to capture (which actions on which objects by which principals). You need both: the audit (destination) and the specification (events).

Click to flip back

Knowledge Check

Leo at SearchWave deploys his application to Azure App Service. The app needs to connect to Azure SQL Database. The security team requires no credentials in code or configuration. What should Leo use?

ASQL authentication with password in Azure Key VaultBSystem-assigned managed identity for the App ServiceCA service principal with a client secret stored in environment variablesDWindows Authentication with a domain service account

Check Answer

🎬 Video coming soon

Next up: Transaction Isolation and Concurrency — understand how transactions interact and prevent blocking and deadlocks.