Calling sp_spaceused from a stored procedure

Question

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 Management 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?

Does the user have permissions to MyTable? Have you tried including the schema name with the table (for example, 'dbo.MyTable')? — Tom H, Jun 23 '11 at 17:29
@Tom H.: It looks like user permissions to the table is the issue. I granted full access to the user that is doing the call from code and it worked. Just need to go back now and figure out the minimum level of permissions I need to set. If you post your comment as the answer I will accept. — rsbarro, Jun 23 '11 at 17:35

score 4 · Answer 1 · answered Jun 23 '11 at 17:33

4

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.

answered Jun 23 '11 at 17:33

Aaron Bertrand

272,866
37
466
490

+1, the procedure `sp_spaceused` uses `sys.dm_db_partition_stats` to get some values, check out the source code in SSMS, go to database msdb, then programmability, then stored procedures, then system stored procedures, then sys.sp_spaceused, then modify. you can see everything they are doing. Just include everything in your sql code that you need from sp_spaceused and be on your way. – KM. Jun 23 '11 at 17:46
+1 for EXECUTE AS, however that approach will not work as well as just granting access to the table for my particular scenario. Thanks for the answer. – rsbarro Jun 23 '11 at 18:25

score 1 · Answer 2 · answered Jun 23 '11 at 17:31

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.

score 1 · Accepted Answer · answered Jun 23 '11 at 17:43

1

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.

answered Jun 23 '11 at 17:43

Tom H

46,766
14
87
128

From my testing, I was able to execute sp_spaceused by granting either `Select`, `View Definition`, or `View Change Tracking` permissions (the last one I did not expect to work). It almost seems like you just need to grant any access to the table for sp_spaceused to work for that user. I'm going to go with SELECT access since the data is not sensitive. Thanks for the help! – rsbarro Jun 23 '11 at 18:20
From the sounds of it, I would think that View Definition would make more sense, but it's obviously your call – Tom H Jun 23 '11 at 18:40
@rsbarro_ you don't need to GRANT *any* permissions if you use a UDF to hide the sys calls. As per my anser – gbn Jun 23 '11 at 19:01
@gbn Noted, I was just looking for a solution where it was all contained in one stored procedure. I may consider setting up a UDF for the purpose of getting counts in the future. Thanks. – rsbarro Jun 23 '11 at 22:27

score 1 · Answer 4 · edited May 23 '17 at 12:19

1

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?

edited May 23 '17 at 12:19

Community

1
1

answered Jun 23 '11 at 18:35

gbn

422,506
82
585
676

Just a suggestion: as it seems the poster might be running into cross-schema permissions (and as schema usage becomes more prevalent), you should check both object name and schema name rather than just object name alone. In some of my systems your queries would fail because there may be dbo.mytable and some_other_schema.mytable. Also, I don't think you meant to put quotes around '@tablename' in your second snippet. Finally, be careful with really big tables, you may overflow the INT - this is why sp_spaceused, for example, converts to BIGINT in several cases. – Aaron Bertrand Jun 23 '11 at 19:01

Calling sp_spaceused from a stored procedure

4 Answers4