Permissions, Auditing, and Passwordless Access

Who can do what — and who is watching

Simple explanation

Think of a building with key cards, security cameras, and no physical keys.

Permissions are key cards — each person’s card only opens the doors they need. Auditing is the security camera — it records who went where and when. Passwordless access replaces physical keys (passwords that can be stolen) with biometric scanners (managed identity, certificates) that cannot be copied.

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.

SQL Server / Managed Instance auditing

-- Server-level auditing (SQL Server / Managed Instance)
CREATE SERVER AUDIT AuditToBlob
    TO URL (PATH = 'https://vaultbankstorage.blob.core.windows.net/audit');

CREATE SERVER AUDIT SPECIFICATION ServerAuditSpec
    FOR SERVER AUDIT AuditToBlob
    ADD (FAILED_LOGIN_GROUP)
    WITH (STATE = ON);
GO
ALTER SERVER AUDIT AuditToBlob WITH (STATE = ON);
GO

-- Note: Azure SQL Database uses portal/CLI configuration for auditing,
-- not T-SQL audit objects. Database audit specs below still apply.

-- Database-level audit specifications
CREATE DATABASE AUDIT SPECIFICATION AuditSensitiveAccess
FOR SERVER AUDIT [AuditToBlob]
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). Auditing is typically required in addition to permissions and other controls. 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?

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