Grant Access to DMVs in SQL Server for Non Admin
June 09, 2013
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.
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.
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
Geeks who read this article also read…
- How to Backup an Analysis Services Database Using SQL Server Management Studio
- How to Restore an Analysis Services Database Using SQL Server Management Studio
- Automate Backup of Analysis Services Database Using SQL Server Agent Job
- How to Attach Analysis Services Database in SQL Server
- How to Detach Analysis Services Database in SQL Server
- Performance Dashboard Reports in SQL Server 2012
- How to Backup Database in SQL Server
- How to Restore Database in SQL Server
- How to Attach Database Without a Transaction Log File in SQL Server
- New Features in SQL Server 2012 Reporting Services for Developers
- New Features in SQL Server 2012 for Database Administrators
- New Features in SQL Server 2012 for Database Developers
- Read More SQL Server Articles…