Calling sp_spaceused from a stored procedure

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:

--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)

This SQL works fine when I execute the stored procedure from SQL 开发者_StackOverflowManagement Studio using the Administrator account. However, when I try to execute the stored procedure from code (which uses a different login) sp_spaceused fails with the message "The object 'MyTable' does not exist in database 'MyDatabase' or is invalid for this operation."

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?

Did you try using the object prefix (e.g. EXEC sp_spaceused 'dbo.MyTable')? This can happen if the user does not have access to the object's schema or has a different default schema than dbo.

Did you try creating the procedure with EXECUTE AS?

As an aside, rather than call this stored procedure over and over again, why not pull the data from sys.dm_db_partition_stats where index_id IN (0,1)? This has the page counts you're after (which make it easy to derive the same info) and doesn't require all the scaffolding of executing a procedure to dump data into a #temp table.

You need to make sure that the calling user has permissions to the table which you're checking. Read permissions should be sufficient for what you're doing, but I haven't tested that.

As others have said, you can also use EXECUTE AS if the data in the table is of a secure nature and you don't want the user to have even read access to it.

Are you sure you are in the correct database when you are running outside SSMS?

Although the docs say "Permission to execute sp_spaceused is granted to the public role.", that appears to contradict what you are seeing.

One option is to put it in a stored procedure and use the EXECUTE AS feature to get it to execute as an administrator.

There are simpler ways.

Given sp_spaceused internally calls sys.dm_db_partition_stats, you can run this:

SELECT
   @rowCount = SUM(st.row_count)
FROM
   sys.dm_db_partition_stats st
WHERE
   object_name(object_id) = 'Mytable' AND (index_id < 2)

You can hide permissions by using a udf

CREATE FUNCTION dbo.GetCounts (@tablename varchar(100))
RETURNS bigint
WITH EXECUTE AS OWNER
AS
BEGIN
    RETURN (
    SELECT
       SUM(st.row_count)
    FROM
       sys.dm_db_partition_stats st
    WHERE
       object_name(object_id) = @tablename AND (index_id < 2)
    )
END
GO

More in my answer here: Fastest way to count exact number of rows in a very large table?