September 28, 2010

Security permission to see other session's information

In order to query to see other session information, a user needs specific permissions granted. To view the server-wide DMVs the user must be granted the VIEW SERVER STATE on the server. After running the following query as administrator

To query database specific DMVs a user must be granted the VIEW DATABASE STATE permission in each specific database.

If you want to deny a user permission to query certain DMVs you can use the DENY command and reference those specific views. And remember it's always better to grant permission to roles instead of individual users.