SQL Server query fails in newer version with "VIEW DATABASE PERFORMANCE STATE permission denied" error, despite only requiring "VIEW DEFINITION" permission

Hi @Hamza Mohammad

The above tables mentioned

Actually, they are not tables, they are system catalog views. See: Query Store catalog views.

And the VIEW DATABASE STATE permission is required to use these views.

See this doc: sys.query_store_plan (Transact-SQL).

What could be the cause of this discrepancy in required permissions between the two SQL Server versions?

Please double check the permission of these two different users.

Best regards,

Cosmog

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

I don't think there is no difference. Both in Synapse and Azure SQL Database, you need the permission VIEW DATABASE PERFORMANCE STATE to view Query Store data. Try this in Azure SQL Database:

CREATE USER tempuser WITHOUT LOGIN
GRANT VIEW DEFINITION TO tempuser
go
EXECUTE AS USER = 'tempuser'
go
SELECT Txt.query_text_id, Txt.query_sql_text, Pln.plan_id, Qry.*, RtSt.*
FROM sys.query_store_plan AS Pln
INNER JOIN sys.query_store_query AS Qry
    ON Pln.query_id = Qry.query_id
INNER JOIN sys.query_store_query_text AS Txt
    ON Qry.query_text_id = Txt.query_text_id
INNER JOIN sys.query_store_runtime_stats RtSt
ON Pln.plan_id = RtSt.plan_id;
go
REVERT
go
DROP USER tempuser

I would guess you are confused about the permissions of your test user in Azure SQL Databases. Presumably, the user is member of a role with the required permissions.