title: Calling sp_spaceused from a stored procedure
body: <p>In SQL Server 2008, I'm trying to quickly get a row count on a table for diagnostic purposes in a stored procedure. Within the stored proc I'm using the following SQL to get a count without doing a table scan:</p>
  
  <pre><code>--Get row count using sp_spaceused
  DECLARE @rowCount AS INT
  DECLARE @spaceUsed TABLE(
      [Name] varchar(64), 
      [Rows] INT,
      [Reserved] VARCHAR(50),
      [Data] VARCHAR(50),
      [Index_Size] VARCHAR(50),
      [Unused] VARCHAR(50)
  )
  INSERT INTO @spaceUsed EXEC sp_spaceused 'MyTable'
  SET @rowCount = (SELECT TOP 1 [Rows] FROM @spaceUsed)
  </code></pre>
  
  <p>This SQL works fine when I execute the stored procedure from SQL Management Studio using the Administrator account. However, when I try to execute the stored procedure from code (which uses a different login) <code>sp_spaceused</code> fails with the message "The object 'MyTable' does not exist in database 'MyDatabase' or is invalid for this operation."</p>
  
  <p>Is there a way to make this work for a non-adminstrator login? Is there something else I'm missing about running the procedure in Management Studio vs. from code?</p>