Get required permissions for DMVs

Part of my job at work is to update Transact-SQL reference content. System dynamic management views (DMVs) have permissions that are managed in the SQL Server Database Engine source code, so it’s a little tricky to figure out the required permissions for a specific DMV.

This blog post provides a stored procedure that uses sys.fn_builtin_permissions to calculate permissions, but keep in mind that any custom permissions that you’ve implemented, can override the defaults.

SQL Server 2022 introduced new PERFORMANCE and SECURITY permission levels, so think of the first column as >= 2022, and the second column as <= 2019.

You still need SELECT on the DMV itself (system objects grant this by default unless explicitly revoked).

On Azure SQL Database, you can’t grant server-level permissions. Instead, you need built-in roles (for example, ##MS_ServerStateReader##).

Enjoy! This code isn’t the greatest, but it seems to work. It’s free for anyone to use, and no attribution is necessary.

-- Run once in any user DB
CREATE OR ALTER PROCEDURE dbo.DmvPermission
@dmv sysname, @principal sysname = NULL
AS
BEGIN
    SET NOCOUNT ON;

    -- Normalize
    DECLARE @bare AS sysname = CASE WHEN LEFT(@dmv, 4) = 'sys.' THEN SUBSTRING(@dmv, 5, 255) ELSE @dmv END;

    -- Is it a real system view here?
    IF NOT EXISTS (SELECT 1
                   FROM sys.system_views
                   WHERE name = @bare)
        BEGIN
            RAISERROR ('"%s" is not a system view on this instance.', 16, 1, @dmv);
            RETURN;
        END

    DECLARE @is_db_scoped AS BIT = CASE WHEN @bare LIKE N'dm_db\_%' ESCAPE '\' THEN 1 ELSE 0 END;

    -- Heuristics: scope + security-ish DMVs
    DECLARE @is_security AS BIT = CASE WHEN @bare LIKE N'dm[_]%audit[%]%' ESCAPE '\'
                                            OR @bare LIKE N'dm[_]%security[%]%' ESCAPE '\' THEN 1 ELSE 0 END;
    DECLARE @perm AS sysname, @superset AS sysname, @scope AS sysname;
    SET @scope = CASE WHEN @is_db_scoped = 1 THEN 'DATABASE' ELSE 'SERVER' END;
    IF @is_db_scoped = 1
        BEGIN
            IF @is_security = 1
               AND EXISTS (SELECT 1
                           FROM sys.fn_builtin_permissions('DATABASE')
                           WHERE permission_name = 'VIEW DATABASE SECURITY STATE')
                SET @perm = 'VIEW DATABASE SECURITY STATE';
            ELSE
                IF EXISTS (SELECT 1
                           FROM sys.fn_builtin_permissions('DATABASE')
                           WHERE permission_name = 'VIEW DATABASE PERFORMANCE STATE')
                    SET @perm = 'VIEW DATABASE PERFORMANCE STATE';
                ELSE
                    SET @perm = 'VIEW DATABASE STATE';
            SET @superset = CASE WHEN @perm IN ('VIEW DATABASE PERFORMANCE STATE', 'VIEW DATABASE SECURITY STATE') THEN 'VIEW DATABASE STATE' END;
        END
    ELSE
        BEGIN
            IF @is_security = 1
               AND EXISTS (SELECT 1
                           FROM sys.fn_builtin_permissions('SERVER')
                           WHERE permission_name = 'VIEW SERVER SECURITY STATE')
                SET @perm = 'VIEW SERVER SECURITY STATE';
            ELSE
                IF EXISTS (SELECT 1
                           FROM sys.fn_builtin_permissions('SERVER')
                           WHERE permission_name = 'VIEW SERVER PERFORMANCE STATE')
                    SET @perm = 'VIEW SERVER PERFORMANCE STATE';
                ELSE
                    SET @perm = 'VIEW SERVER STATE';
            SET @superset = CASE WHEN @perm IN ('VIEW SERVER PERFORMANCE STATE', 'VIEW SERVER SECURITY STATE') THEN 'VIEW SERVER STATE' END;
        END
    SELECT 'sys.' + @bare AS DMV,
           @scope AS Scope,
           @perm AS RequiredPermission,
           @superset AS SupersetPermissionThatAlsoWorks,
           CASE WHEN @is_db_scoped = 1 THEN HAS_PERMS_BY_NAME(DB_NAME(), 'DATABASE', @perm) ELSE HAS_PERMS_BY_NAME(NULL, NULL, @perm) END AS YouHaveIt;

    -- Optional GRANT script
    IF @principal IS NOT NULL
        BEGIN
            IF @is_db_scoped = 1
                SELECT 'GRANT ' + @perm + ' TO ' + QUOTENAME(@principal) + ';' AS GrantScript;
            ELSE
                SELECT 'USE master; GRANT ' + @perm + ' TO ' + QUOTENAME(@principal) + ';' AS GrantScript;
        END
END
GO

Here are some examples:

EXECUTE dbo.DmvPermission @dmv = 'sys.dm_exec_sessions';
EXECUTE dbo.DmvPermission @dmv = 'sys.dm_server_audit_status';
EXECUTE dbo.DmvPermission @dmv = 'sys.dm_db_index_usage_stats';
EXECUTE dbo.DmvPermission @dmv = 'sys.dm_exec_requests', @principal = 'MyLogin';

If you want to generate the full list of DMVs to check, here’s a little script to generate a script to run the stored procedure.

SELECT 'EXEC dbo.DmvPermission @dmv=''sys.' + name + ''';'
FROM sys.system_objects
WHERE name LIKE 'dm_%' AND type = 'V';

1 thought on “Get required permissions for DMVs”

Eric Zierdt September 3, 2025 at 11:43 am


Very Cool. I decided to dump all the results into a table so I could query/see it easier, here’s some code to do that:

CREATE TABLE #DMVResults (
ID INT IDENTITY(1,1)
, DMV VARCHAR(256)
, Scope VARCHAR(50)
, RequiredPermissions VARCHAR(256)
, SupersetPermissionThatAlsoWorks VARCHAR(256)
, YouHaveIt BIT
)

DECLARE @DMVCode VARCHAR(120)
DECLARE ZCursor CURSOR LOCAL FAST_FORWARD FOR
(
SELECT ‘EXEC dbo.DmvPermission @dmv=”sys.’ + name + ”’;’
FROM sys.system_objects
WHERE name LIKE ‘dm_%’ AND type = ‘V’
)
OPEN ZCursor
FETCH NEXT FROM ZCursor INTO @DMVCode
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #DMVResults (
DMV
, Scope
, RequiredPermissions
, SupersetPermissionThatAlsoWorks
, YouHaveIt
)
EXEC (@DMVCode)
FETCH NEXT FROM ZCursor INTO @DMVCode
END

CLOSE ZCursor
DEALLOCATE ZCursor

SELECT *
FROM #DMVResults
WHERE RequiredPermissions ‘VIEW SERVER STATE’

Get required permissions for DMVs

1 thought on “Get required permissions for DMVs”

Leave a Reply Cancel reply