title: How do I grant limited access to sysprocesses in SQL Server 2005?
body: <p>I'm implementing some rudimentary SQL Server monitoring to watch for excessive blocking.</p>
  
  <p>I have a stored procedure that looks like this:</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[bsp_GetBlockedProcessCount]
  AS
  BEGIN
      SET NOCOUNT ON;
  
      DECLARE @count INT
  
      SELECT @count = COUNT(*)
      FROM master.dbo.sysprocesses WITH (NOLOCK)
      WHERE blocked &gt; 0
  
      RETURN @count
  END
  </code></pre>
  
  <p>When I execute this in an administrator context, I get the correct answer. When I execute this in the context of a user with limited privileges (i.e. the webserver), I get zero irrespective of the true answer.</p>
  
  <p>I can think of a few solutions but I'm not happy any of them:</p>
  
  <ol>
  <li><p>I can grant VIEW SERVER STATE privileges to the user but that's way more permission than this user should have:</p>
  
  <pre><code>GRANT VIEW SERVER STATE TO [Webserver]
  </code></pre></li>
  <li><p>Run the proc from a SQL job and put the results somewhere for the webserver to pick up but that's messy and complicated.</p></li>
  <li>Don't try and call this from the webserver!</li>
  </ol>
  
  <p>I'm sure there's a better solution. Any suggestions?</p>