SQL Server – Minimal Permissions to Read Index Size

permissionssql serversql server 2014

I'm involved with managing an application with a large number of SQL Server 2014 databases and am extracting table and index sizes in order to track capacity. I'm using the following query:

select
    db_name() as DBName,
    object_schema_name(i.object_id) as SchemaName,
    object_name(i.object_id) as TableName,
    isnull(i.name,'(HEAP)') as IndexName,
    ps.SizeInPages
from
    sys.indexes i
        inner join 
        (
            select ps.object_id,ps.index_id,sum(ps.row_count) as IndexRows, sum(ps.used_page_count) as SizeInPages, count(*) as PartitionCount
            from sys.dm_db_partition_stats ps
            group by ps.object_id,ps.index_id
        ) ps on 
            ps.object_id = i.object_id and
            ps.index_id = i.index_id
where
    object_schema_name(i.object_id) not in ('cdc','sys')

Ideally I'd like to run this query from a SQL Login with the minimum required permissions, but so far it seems like the user needs VIEW SERVER STATE and SELECT permissions on all the tables. I've also tried putting the above query in a stored procedure and giving the user account EXECUTE permissions on that, but sys.indexes is still filtered based on what the user has select rights to.

Is there another way of doing this that I'm missing?

Best Answer

As far as i know, the minimal required permission set solution in this case is to create a store procedure thats executed within an impersonated context. Do this by:

create procedure [schema].[get_idx_info] 
with execute as [user with view database state perms and select perms or OWNER]
as
begin
  select
   db_name() as DBName,
   object_schema_name(i.object_id) as SchemaName,
   object_name(i.object_id) as TableName,
   isnull(i.name,'(HEAP)') as IndexName,
   ps.SizeInPages
  from
    sys.indexes i
    inner join 
    (
        select ps.object_id,ps.index_id,sum(ps.row_count) as IndexRows, sum(ps.used_page_count) as SizeInPages, count(*) as PartitionCount
        from sys.dm_db_partition_stats ps
        group by ps.object_id,ps.index_id
    ) ps on 
        ps.object_id = i.object_id and
        ps.index_id = i.index_id
   where
   object_schema_name(i.object_id) not in ('cdc','sys')        
end

see: (https://docs.microsoft.com/en-us/sql/t-sql/statements/execute-as-clause-transact-sql)

Then grant users execute on that stored procedure. This will allow the user to view that info w/o additional permissions (aside from EXECUTE on the SP).

to test:

create login [nonprivileged] with password = 'eqreADf$a23asd';
go
create user [nonprivileged];
go
grant execute on [schema].[get_idx_info]  to [nonprivileged];
go

execute as user = 'nonprivileged';
/* should work */
exec [schema].[get_idx_info];
revert;

execute as user = 'nonprivileged';
/* should fail */
select * from sys.dm_db_partition_stats
revert;

execute as user = 'nonprivileged';
/* should fail */
select * from sys.indexes;
revert;

drop user [nonprivileged];
go

drop login [nonprivileged];
go

By doing this, the stored procedure will execute with the permissions of either the designated user or (if set to OWNER) the creator/owner of the stored procedure, instead of the permissions of the user calling EXECUTE (this is default behavior, which is why sys.indexes is filtering by select permissions in your case).

Related Question