system info permissions | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

system info permissions

In SQL Server 2005, if I log in to Management Studio as sa, I can run Select * from SysProcesses and all the processes are returned. If I log in as a user without sa rights, the result set only shows my own connection. The same thing happens when I run sp_who and Select * from sys.dm_exec_sessions. I have granted the public role Select on SysProcesses but that didn’t work. Nor did placing the Select statement into a stored procedure and granting the user execute rights to the sproc. Does anyone have any ideas, or can you send me in the right direction? Thanks, Rod Harten
After further research, SQL Server 2005 has an additional level of permissions called server permissions. In this case, I need to grant to the users the VIEW SERVER STATE permission. The code is: USE Master
GRANT View Server State TO [UserName]