How do you detect whether you have permission to query sys.dm_os_performance_counters?
Eventually we worked it out!


If you're connected to SQL Azure Database, and the database tier is Premium, check for the VIEW DATABASE STATE permission
If you're connected to SQL Azure Database, and the database tier is not Premium, check for EXECUTE permission on sys.dm_os_performance_counters
Otherwise, check for the VIEW SERVER STATE permission


The bit of this that isn't an obvious translation of the documentation is that "EXECUTE permission on sys.dm_os_performance_counters" is equivalent to "being a Server admin or an Azure Active Directory admin" in the case of non-Premium Azure SQL Database. This was just what we found by trial and error, and is not documented anywhere that I'm aware of.

DECLARE @Result VARCHAR(MAX)
SET @Result = ''

IF (SERVERPROPERTY('EngineEdition') = 5)
    BEGIN
        IF (DATABASEPROPERTYEX(DB_NAME(), 'Edition') = 'Premium')
            BEGIN
                IF NOT EXISTS(SELECT 1 FROM sys.fn_my_permissions(NULL, 'DATABASE') WHERE permission_name = 'VIEW DATABASE STATE')
                    SET @Result = 'You do not have VIEW DATABASE STATE permissions for this database.'
            END
        ELSE
            BEGIN
                IF NOT EXISTS(SELECT 1 FROM sys.fn_my_permissions('sys.dm_os_performance_counters', 'OBJECT') WHERE permission_name = 'EXECUTE')
                    SET @Result = 'You do not have server admin or Azure active directory admin permissions.'
            END
    END
ELSE
    BEGIN
        IF NOT EXISTS(SELECT 1 FROM sys.fn_my_permissions(NULL, 'SERVER') WHERE permission_name = 'VIEW SERVER STATE')
            SET @Result = 'You do not have VIEW SERVER STATE permissions within this instance.'
    END
RETURN @Result