Post reply

sp_spaceused without VIEW DATABASE STATE permission

  • February 3, 2011 at 1:13 am

    #234389

    Hi,

    I have tried to search the forums for this, but did not find a match. Apologies in advance if I have overlooked it.

    I am currently working on an application that needs to identify the rowcounts of some tables.

    My initial decision was to use sys.dm_db_partition_stats. But this requires VIEW DATABASE STATE permission, and our DBAs are hesitant to grant this permission if there is a workaround available. So they suggested to use sp_spaceused instead.

    Now the strange thing - sp_spaceused is using sys.dm_db_partition_stats under the hoods as well. So in my theory, this should not work, but testing turned out that it does work - with no "EXECUTE AS" involved.

    Here is the header of the procedure, which does not contain the EXECUTE AS clause:

    create procedure sys.sp_spaceused --- 2003/05/19 14:00

    @objname nvarchar(776) = null,-- The object we want size on.

    @updateusage varchar(5) = false-- Param. for specifying that

    -- usage info. should be updated.

    as

    I have also tested to create a copy of this procedure in a user schema (instead of sys) - and if I do that, the procedure fails with the expected error The user does not have permission to perform this action.

    Is this behaviour of sys.sp_spaceused expected? Do procedures in the sys schema run with elevated rights per default?

    And is there any documentation on this topic? I tried to find the needle, but the haystack is too big.

    Best Regards,

    Chris Büttner

  • February 3, 2011 at 1:56 am

    #1281757

    Regarding getting row counts there was an article in SQL Server Central recentlly.

    http://www.sqlservercentral.com/articles/T-SQL/67624/

    On your question regarding access to view meta data, please refer below link. It has all the details.

    From SQL Server 2005 onwards, we can't view meta data easily like in earlier versions.

    http://msdn.microsoft.com/en-us/library/ms187113.aspx

    M&M

  • February 3, 2011 at 2:19 am

    #1281765

    Hi Mohammed,

    thank you for responding, but I think you may have misunderstood my question.

    But I woke up now anyways. The explanation is simple: Ownership chains.

    http://msdn.microsoft.com/en-us/library/ms188676.aspx

    Best Regards,

    Chris Büttner

  • February 3, 2011 at 3:24 am

    #1281785

    Hi Chris,

    Thanks for clarifying and sharing that useful link.

    M&M

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply