SQL Server Performance

system info permissions

Discussion in 'SQL Server 2005 General DBA Questions' started by rharten, May 30, 2006.

  1. rharten New Member

    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
  2. rharten New Member

    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]
    GO

Share This Page