How to Deny Access to Specific DMVs in SQL Server for Users who have VIEW SERVER STATE Permissions
Nov 25, 2014
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
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
'DENY SELECT ON sys.' + name + ' TO [Domain\UserNameOrGroupName];'
WHERE name LIKE 'dm_os_%' ORDER BY name ASC
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.
- 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”
MORE SQL SERVER PRODUCT REVIEWS & SQL SERVER NEWS