Azure Says Yes. SQL Server Says No.

Azure IAM says you're a Contributor. SSMS says this:

Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'dm_exec_query_stats', 
database 'ContosoDB', schema 'sys'.

Welcome to cloud permissions, where 'Contributor' doesn't mean you can contribute and 'Reader' doesn't mean you can read.

In my last post, I explained the management plane vs data plane split. This post is the promised follow-up for the minimum permission combinations for common DBA tasks. aka, what you need, how to verify it, and how to fix it when it fails.

The Two-Layer Reality

Quick refresher. Azure has two separate permission systems:

Layer	Controls	Assigned Where
Management Plane (ARM)	Portal visibility, configuration, scaling	Azure IAM
Data Plane (SQL)	Queries, data access, DMVs	Inside the database

These two systems don't talk to each other. Azure doesn't tell SQL Server what you can do. SQL Server doesn't check Azure IAM. You need both configured — separately.

The Permission Matrix

Because you need permissions in both systems, here's what each task requires:

Task	Management Plane (ARM)	Data Plane (SQL)
See database in portal	Reader	—
Query tables	—	db_datareader
Modify data	—	db_datawriter
Query DMVs (performance)	—	VIEW DATABASE STATE
Query server-level DMVs	—	##MS_ServerStateReader##
Create/alter objects	—	db_ddladmin
Manage users in database	—	db_securityadmin
Create logins (server level)	—	##MS_LoginManager##
Scale database / change tier	SQL DB Contributor	—
Configure firewall rules	SQL Server Contributor	—
View metrics in portal	Monitoring Reader	—
Configure alerts	Monitoring Contributor	—
Access blob storage (manual exports)	Reader	Storage Blob Data Contributor

Portal tasks need ARM roles. Query tasks need SQL roles. Some tasks need both.

Note: This matrix applies to both Azure SQL Database and Azure SQL Managed Instance. MI supports a few additional features (ie., SQL Agent, db_backupoperator) but the core permission model is the same.

Permission Combinations by Role

Here's what common DBA roles actually need — both layers combined:

DBA Role	Management Plane (ARM)	Data Plane (SQL)
Monitor only	Reader, Monitoring Reader	VIEW DATABASE STATE
Troubleshoot + kill sessions	Reader, Monitoring Reader	VIEW DATABASE STATE, ALTER ANY CONNECTION
Operational DBA	Reader, Monitoring Reader	db_datareader, db_ddladmin, VIEW DATABASE STATE, ALTER ANY CONNECTION
Full control	SQL DB Contributor, Monitoring Contributor	db_owner

Start with the minimum. Add permissions as the job requires.

Verify Your Access

When your cloud team grants access and walks away, don't assume it's all good. Test all layers before you call it done.

Check	How to Verify
Can see resource in portal?	Navigate to Azure SQL in portal
Can connect via SSMS?	Test connection with your credentials
Can query user tables?	SELECT TOP 1 * FROM any_table
Can query DMVs?	SELECT * FROM sys.dm_exec_requests
Can see portal metrics?	Open Monitoring blade, check for data

Not sure what permissions you actually have? Run this in your Azure SQL Database:

-- What database permissions do I have?
SELECT * FROM sys.fn_my_permissions(NULL, 'DATABASE');

-- Am I in any server roles?
SELECT 
    r.name AS role_name,
    CASE WHEN IS_SRVROLEMEMBER(r.name) = 1 THEN 'Yes' ELSE 'No' END AS member
FROM sys.server_principals r
WHERE r.type = 'R' AND r.name LIKE '##MS_%##';

(The ##MS_ server roles are specific to Azure SQL Database.)

If any check fails, you know exactly which layer is missing.

When It Doesn't Work

Azure says connected. SQL Server says denied.

Symptom: Database appears in portal. SSMS connects. SELECT fails.

Cause: Missing data plane role.

Fix (Data Plane):

-- Run in the target database
ALTER ROLE db_datareader ADD MEMBER [your_user];

You can see tables. You can't see performance.

Symptom: sys.dm_exec_query_stats returns permission denied or empty results.

Cause: Missing VIEW DATABASE STATE (or VIEW SERVER STATE for server-level DMVs).

Fix (Data Plane):

-- Database level
GRANT VIEW DATABASE STATE TO [your_user]; 

-- Server level (for cross-database DMVs)
ALTER SERVER ROLE ##MS_ServerStateReader## ADD MEMBER [your_login];

NOTE:  Reminder, role-based perms are smarter.  
       Create a role, enlist your users, grant perms to said role, 
       add role to ##MS_ServerStateReader##.

Contributor doesn't mean what you think.

Symptom: SQL DB Contributor role assigned. Metrics blade is empty or shows errors.

Cause: Contributor doesn't include monitoring permissions.

Fix (Management Plane): Add Monitoring Reader role in Azure IAM.

The Bottom Line

Like I said before, these two systems don't talk to each other, and they don't share the same permission system. This is how the cloud works. Once you stop expecting them to agree, the Access Denied mysteries disappear.

Keep the matrix handy. Verify before you trust. Be sure you're fixing the right layer.

References

Microsoft: Server-level roles in Azure SQL Database
Microsoft: Database-level roles
Microsoft: Authorize database access
Microsoft: Monitor performance using DMVs
sqlfingers: Management Plane vs Data Plane
sqlfingers: Cloud Security for SQL Server