Security Principals, Permissions, and T-SQL

Security principals and permissions

Security principal hierarchy

Level	Principal	Created With	Scope
Server	Login	CREATE LOGIN	Server-wide (MI, SQL VMs)
Server	Server role	CREATE SERVER ROLE	Groups of logins
Database	User	CREATE USER	Single database
Database	Database role	CREATE ROLE	Groups of users
Database	Application role	CREATE APPLICATION ROLE	Application-specific access

Key difference — SQL Database vs MI:

• Azure SQL Database: No server-level logins. Uses contained database users only. Each database manages its own users independently.
• Azure SQL MI: Supports both server-level logins AND database users (like on-prem SQL Server).
• SQL on VMs: Full server and database security model.

Built-in database roles

Role	Permissions	Use Case
db_owner	Full control over the database	Database administrators
db_datareader	SELECT on all tables and views	Reporting users, read-only access
db_datawriter	INSERT, UPDATE, DELETE on all tables	Applications that modify data
db_ddladmin	CREATE, ALTER, DROP any object	Schema management
db_securityadmin	Manage role membership and permissions	Delegated security management
db_denydatareader	Cannot SELECT any data	Override: block reading even if other roles allow it
db_denydatawriter	Cannot modify any data	Override: block writing even if other roles allow it

💡 DENY always wins

In SQL Server, DENY overrides GRANT. If a user is in both db_datareader (grants SELECT) and db_denydatareader (denies SELECT), the DENY wins — they cannot read data.

The exam tests this: “A user is a member of Role A (GRANT SELECT) and Role B (DENY SELECT). Can they read data?” → No. DENY always takes precedence.

Managing permissions with GUI tools

The exam explicitly tests GUI-based permission management — not just T-SQL.

Azure Portal

• Navigate to the SQL database → Query editor (preview) to run permission T-SQL
• Use Microsoft Entra admin settings to set the Entra admin
• Azure RBAC roles (Contributor, Reader) control Azure management plane — NOT database access

SQL Server Management Studio (SSMS)

• Right-click a database → Properties → Permissions tab
• Right-click a user → Properties → Securables page
• Visual grant/deny/revoke interface for each object
• User Mapping tab on login properties to see database access

Azure Data Studio

• Object Explorer → expand database → Security → Users
• Right-click for properties and permission management
• Less GUI options than SSMS, but growing

💡 Exam tip: Azure RBAC vs database permissions

Azure RBAC (Contributor, Reader, SQL DB Contributor) controls who can manage the Azure resource (create/delete databases, change settings). It does NOT grant access to data inside the database.

Database permissions (GRANT, DENY, db_datareader, etc.) control who can read/write data and manage objects inside the database.

A user with Azure Contributor role on a SQL Database can delete the entire database but cannot SELECT from any table unless they also have database-level permissions.

Managing permissions with T-SQL

Creating users and granting permissions

-- Create a contained database user (SQL Database)
CREATE USER [analyst@harbourhealth.com] FROM EXTERNAL PROVIDER;

-- Add to a built-in role
ALTER ROLE db_datareader ADD MEMBER [analyst@harbourhealth.com];

-- Grant specific object permission
GRANT SELECT ON SCHEMA::Sales TO [analyst@harbourhealth.com];

-- Create a custom role
CREATE ROLE ReportingRole;
GRANT SELECT ON SCHEMA::Sales TO ReportingRole;
GRANT SELECT ON SCHEMA::Inventory TO ReportingRole;
ALTER ROLE ReportingRole ADD MEMBER [analyst@harbourhealth.com];

Permission statements

Statement	Effect
GRANT	Gives a permission (can be overridden by DENY)
DENY	Explicitly blocks a permission (overrides GRANT)
REVOKE	Removes a previously granted or denied permission (returns to default state)

Checking existing permissions

-- What permissions does a user have?
SELECT * FROM sys.fn_my_permissions(NULL, 'DATABASE');

-- What role memberships exist?
SELECT dp.name AS principal, dr.name AS role_name
FROM sys.database_role_members rm
JOIN sys.database_principals dp ON rm.member_principal_id = dp.principal_id
JOIN sys.database_principals dr ON rm.role_principal_id = dr.principal_id;

Applying least privilege

Amara enforces least privilege at Harbour Health:

Principle: Every user and application gets the minimum permissions needed to do their job — nothing more.

Scenario	Bad Practice	Least Privilege
Reporting user needs to read Sales data	Add to db_owner	GRANT SELECT ON SCHEMA::Sales
Application needs to insert orders	Add to db_datawriter (all tables)	GRANT INSERT ON dbo.Orders
DBA needs to manage indexes	Add to sysadmin (server-wide)	Add to db_ddladmin (database-scoped)
Backup operator	Add to db_owner	Add to db_backupoperator

Amara’s rules:

1. Never use sa or sysadmin for applications
2. Custom roles over built-in roles (more specific)
3. Schema-level grants over database-level grants
4. Review permissions quarterly
5. Use DENY sparingly — only when you need to override inherited grants

Troubleshooting auth issues

Tomas encounters these common problems:

Symptom	Likely Cause	Fix
”Login failed for user”	Wrong credentials or login doesn’t exist	Verify login exists; check password; verify Entra auth is configured
”Cannot open database requested”	User has login but no database user mapping	CREATE USER in the target database
”SELECT permission denied”	User lacks read permission	GRANT SELECT or add to db_datareader
”User not found” (after migration)	Orphaned user — no matching login	ALTER USER … WITH LOGIN
”Entra auth failed”	Entra admin not set, or token expired	Set Entra admin; check token cache; verify Entra tenant

-- Check if a user exists in the database
SELECT name, type_desc, authentication_type_desc
FROM sys.database_principals
WHERE name LIKE '%tomas%';

-- Check server logins (MI/VMs only)
SELECT name, type_desc, is_disabled
FROM sys.server_principals
WHERE name LIKE '%tomas%';

Question

What happens when a user has both GRANT SELECT and DENY SELECT through different roles?

Click or press Enter to reveal answer

Answer

DENY always wins. The user cannot SELECT data. DENY overrides GRANT regardless of how the permissions were assigned.

Click to flip back

Question

What is the difference between Azure RBAC and database permissions?

Click or press Enter to reveal answer

Answer

Azure RBAC controls management plane access (create/delete/configure Azure resources). Database permissions control data plane access (SELECT, INSERT, UPDATE inside the database). They are separate systems.

Click to flip back

Question

What T-SQL statement removes a previously applied GRANT or DENY?

Click or press Enter to reveal answer

Answer

REVOKE. It returns the permission to its default (inherited) state. REVOKE SELECT ON dbo.Orders FROM [user];

Click to flip back

Knowledge Check

Tomas creates a custom role and grants it SELECT on the Sales schema. He adds a user to this role AND to db_denydatareader. Can the user read Sales data?

AYes — the custom role grant overrides the denyBYes — schema-level grants take priority over database-level deniesCNo — DENY always overrides GRANT regardless of sourceDNo — custom roles have lower priority than built-in roles

Check Answer

Knowledge Check

Amara's Azure account has the SQL DB Contributor role on the Harbour Health database server. Can she run SELECT queries on patient data?

AYes — SQL DB Contributor grants full database accessBYes — but only through the Azure Portal query editorCNo — Azure RBAC controls management plane, not data accessDNo — she needs the SQL Security Manager role

Check Answer

🎬 Video coming soon

Next up: Encryption: TDE, Always Encrypted, and VBS Enclaves — protect data at rest with multiple layers of encryption.