Grant Access to DMVs in SQL Server for Non Admin


June 09, 2013

Problem

This is a very common question which our readers have asked us on Twitter and Facebook that how to grant access to SQL Server DMV’s to non administrators. This article outlines the steps a database administrator can follow to grant access to DMVs in SQL Server for non administrators. The step mentioned in this tip is applicable to SQL Server 2005 and higher versions.

Solution

In order to query a dynamic management view or a function the user requires SELECT permission on object and VIEW SERVER STATE or VIEW DATABASE STATE permission.

Newsletter Signup

 

In order to grant access to a user the first step will be to create the user in master and then deny the user SELECT permission on the dynamic management views or functions that you do not want them to access. This way you can make sure that users are not having unnecessary permission to query all the DMV’s.

Sample Script to Grant Server State Permissions to User to query DMVs

USE master;
GO

GRANT VIEW SERVER STATE TO UserName;
GO

Share this Article


Geeks who read this article also read…



Follow @MyTechMantra on Twitter
We're on Facebook
Bookmark and Share

"Receive newsletters and special offers about SQL Server, BizTalk and SharePoint from MyTechMantra. We respect your privacy and you can unsubscribe at any time."