Technical Article

Grant Sp_who2, & who3 proc execution without granting sysadmin access

,

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.

---Grant Server state view access to user
GRANT VIEW SERVER STATE TO User_name;

---grant execution permission on procedures

GRANT execute on sp_who2 TO User_name;
GRANT execute on sp_who3 TO User_name;
GRANT execute on sp_whoisactive TO User_name;

---Grant showplan access to user

USE DB_Name
GO
GRANT SHOWPLAN TO User_name;
GO

---create role

USE master

GO
CREATE ROLE [db_showplan] AUTHORIZATION [dbo]
GO

---Grant permission to role

GRANT SHOWPLAN to [db_showplan]

Rate

4 (2)

You rated this post out of 5. Change rating

Share

Share

Rate

4 (2)

You rated this post out of 5. Change rating