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';