SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Grant Sp_who2, & who3 proc execution without granting sysadmin access

By Subhash Chandra,

Its common requirement of Developers to access the Sp_who2, Sp_who3, Sp_whoisactive and SHOWPLAN of executed quires or procedures for check the DB server sessions, health, blocking and execution plan check. But DBA mostly denied granting this access mostly required the sysadmin access to execute this kind of queries.  

We can grant access to these procedures or SHOWPLAN without granting any sysadmin access. For this you have to grant the VIEW SERVER STATE permission and individually procedure execution permissions.

Also, you can make a make a separately db_showplan role for it and can make the member of this group to users.

Total article views: 345 | Views in the last 30 days: 1
Related Articles

Grant execute on stored procedures on master database

Access denied on executing stored procedures


Granting Execute Access to All Stored Procedures to a Given User

A quick and dirty procedure to allow you to grant execute permissions to a user, or role, quickly an...


granting execute permissions to ALL stored procedure

how can I grant access to all stored procedures at once


Grant Execute with Grant issue

Running into some odd behavior when trying to allow a role to grant execute permissions to stored pr...


grant access to all databases

grant access to all databases