Connect With MyTechMantra.com













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


How to Deny Access to Specific DMVs in SQL Server for Users who have VIEW SERVER STATE Permissions


Nov 25, 2014

Introduction

In Grant Access to DMVs in SQL Server for Non Admin tip we discussed how to grant access to DMVs for Non Admins. However, the next set of question which we started receiving through our NewsLetter Subscribers and our Facebook Friends was how to restrict access to certain set of Dynamic Managment Views or Functions. This tip explains How to Deny Access to Specific DMVs in SQL Server for users to whom you have Granted VIEW SERVER STATE Permissions.

Script to find all SQL Server Operating System Related Dynamic Management Views (DMVs)

Execute the below mentioned TSQL query to find all SQL Server Operating System Related Dynamic Management Views (DMVs).

SELECT * FROM sys.sysobjects WHERE name LIKE 'dm_os_%' ORDER BY name ASC
GO


List of SQL Server Operating System Related Dynamic Management Views (DMVs)

Once you GRANT VIEW SERVER STATE permissions to a user then the user will be able to see all the DMVs and DMFs and query them. Exposing too much of information to a user who doesn’t have the knowledge can be sometimes very dangerous. In order to restrict access to users to such views, DBA will have to DENY SELECT access to the Dynamic Management Views (DMVs) or Dynamic Management Functions (DMFs) that you do not want the users to access.

In SSMS, open a New Query window and copy the below mentioned query and change the output as Results to Text (CTRL + T) and specify the User Name or Domain Group within the query. Below query will generate the script to DENY SELECT Permissions to Operating System related DMVs.

DENY SELECT ON Specific Set of DMVs in SQL Server to Restrict Access to DMVs

SELECT
    'DENY SELECT ON sys.' + name + ' TO [Domain\UserNameOrGroupName];'
FROM sys.sysobjects
    WHERE name LIKE 'dm_os_%' ORDER BY name ASC
GO


Generate Script to DENY SELECT ON Specific Set of DMVs in SQL Server

Finally, copy the results of the above query to a New Query window in SSMS and execute the scripts to DENY SELECT to all SQL Server Operating System related DMVs.

"Did this article help you? Then Do Like Us on Facebook! And Signup for Our Newsletter"”

Next Steps

  • Verify if there are any non-admins whom you have granted VIEW SERVER STATE permissions across SQL Server Environments.
  • Document any exceptions within Disaster Recovery Documents.
  • For more information on Best Practices for DBAs and Developers read “SQL Server Best Practices”

Share this Article





LEARN MORE...
 
Winners
White Papers
Product Reviews
Trending News
All Articles
Free Tools
 
Follow Us...