Minimal permissions required to read index size in SQL Server

Question

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?

Answer 1

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://learn.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).

Answer 2

There are couple ways to achieve what you are after.

I will show you two, but there are other ways as well.

The first one is signing a procedure, and allowing other users to use it with specifying grant execute on object::yourprocedure only.

First of all you need a master key, which i assume you already have, and you can check it using sys.symmetric_keys DMV, and looking for record that has '##' as a prefix. If not simply create a DB master key

'CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'P@$$W0RD'

This is very importaint key, and make sure you back it up using this guide

Now you are going to create a certificate based on which you will create module signing user. Note it is only for singing modules and you cannot use it to test privileges etc. So first things first - Certificate :

CREATE CERTIFICATE SignModules
WITH SUBJECT = 'Certificate for signing modules'

Just like the master key, you need to back up this certificate as well. guide

Now we are creating a user with certificate which will have all the rights:

CREATE USER UnlockCertifiedProcedures FOR CERTIFICATE SignModules

There are two ways of providing necessary rights:

• becoming a db_reader as exec sp_addrolemember 'db_datareader',UnlockCertifiedProcedures
• giving appropriate rights GRANT VIEW DEFINITION TO UnlockCertifiedProcedures, GRANT VIEW DATABASE STATE TO UnlockCertifiedProcedures

Once this is done, the next step is to actually sign this procedure, so whoever has rights to 'execute' the procedure will inherit rights from the certificate user.

ADD SIGNATURE TO Schema.YourProcedure
BY CERTIFICATE SignModules

To test:

create user TestUser WITHOUT LOGIN grant execute on OBJECT::YourProcedure to TestUser execute as user = 'TestUser' exec Schema.YourProcedure

The second option is creating a user and executing procedure as that specified user.

CREATE USER AnotherTest WITHOUT LOGIN
GRANT VIEW DEFINITION TO AnotherTest
GRANT VIEW DATABASE STATE To AnotherTest
GRANT EXECUTE ON OBJECT::YourProcedure To AnotherTest

Create procedure executing as that user:

CREATE proc  Schema.YourProcedure WITH EXECUTE AS 'AnotherTest'
AS
--your code--

Now simply give execute rights on a procedure to a specified user :

GRANT EXECUTE ON OBJECT::YourProcedure To SomeUser

Like i said there are other ways, including roles but these two could get you a job done. Just remember you cannot track a user that executed procedure by specifying 'execute as'(login is possible tho). Also creating a certificate/database keys can be a headache when you`re migrating DB, or simply restoring it somewhere else.

Answer 3

I asked the OP if the code is intended to be executed on the whole server, i.e. in all databases or in some of them and still no anwer, so I suppose he needs the code to be executed in ALL databases:

managing an application with a large number of SQL Server 2014 databases I give the solution to all the server.

First, your login should access all the databases, and you accomplish this by granting him CONNECT ANY DATABASE PERMISSION

Then your login should access object definitions without accessing the data (here is a trade off: or you give the read access on all your tables, or you give the access on all definitions, this means all modules included, but no data access in this case, so I prefer the last one), this is given by VIEW ANY DEFINITION to login

These two permissions:

• CONNECT ANY DATABASE PERMISSION

• VIEW ANY DEFINITION

  granted to login at the server level is the minimum set of permissions to find out all sizes of all indexes on the server, just change you code to use sys.allocation_units instead of sys.dm_db_partition_stats

But if you insists on your code using sys.dm_db_partition_stats it requires VIEW DATABASE STATE in every database, and you grant it by granting only 1 time VIEW SERVER STATE permission to your login

............................................ In reply to this one:

sys.indexes contains no rows if the user does not have permissions on any tables...

My user l has no SELECT permission in a database, but it "sees" all the tables(and other objects as well) in a database, and has no problem with sys.indexes while he cannot read any data; all he has in that database is VIEW DEFINITION permission

.....................................................................

Solution for only some databases:

As the OP updated the question saying he's open to another code, and the login is already granted access to the database, I suggest to only grant VIEW DEFINITION permission on database to the user in question and use sys.allocation_units to get out index sizes like this:

select object_name(i.object_id) as tbl_ename,
       i.name as idx_name,
       i.index_id as idx_id,
       cast(sum(a.used_pages) * 8 / 1024. as decimal(20,2)) as size_Mb
from sys.indexes i join sys.partitions p 
         on p.object_id = i.object_id and p.index_id = i.index_id
     join sys.allocation_units a 
         on a.container_id = p.partition_id
group by i.object_id,
         i.index_id,
         i.name
order by object_name(i.object_id),
         i.index_id;

And here is the link to technet article describing VIEW DEFINITION Permission

The VIEW DEFINITION permission lets a user see the metadata of the securable on which the permission is granted. However, VIEW DEFINITION permission does not confer access to the securable itself. For example, a user that is granted only VIEW DEFINITION permission on a table can see metadata related to the table in the sys.objects catalog view. However, without additional permissions such as SELECT or CONTROL, the user cannot read data from the table. For more information about viewing metadata, see GRANT (Transact-SQL).

In our case we grant VIEW DEFINITION on a database, so the user granted has no access to data itself but to all the metadata:

Database Scope

VIEW DEFINITION granted at this scope effectively negates permissions-based metadata access for the grantee in the specified database. This means that the grantee can see all metadata in the database context in which the GRANT statement is executed, unless the grantee is denied VIEW DEFINITION or CONTROL permissions at the schema scope or for an individual entity such as a table. For information about the syntax to use for this permission at this scope, see GRANT (Transact-SQL).

Answer 4

The underlying objects for the sys.indexes view are the following:

• sys.sysidxstats
• sys.syssingleobjrefs
• sys.syspalvalues
• sys.sysschobjs

In order to retrieve data for the sys.indexes view you would have to GRANT SELECT ON <object> TO <user> for all the above objects. Sadly, this is not possible, because of the following restrictions:

The system base tables are used only within the SQL Server Database Engine and are not for general customer use. They are subject to change and compatibility is not guaranteed.

Reference: System Base Tables

It might be easier to assign the user in question with the db_datareader role for each database in order to retrieve the relevant information.

From my testing this morning with a Windows Login on a SQL Server instance, it is not sufficient to have VIEW SERVER STATE or VIEW DATABASE STATE to retrieve the sys.indexes information.

However, applying the db_datareader database role is sufficient to allow access to the sys.indexes view.

result set of sys.indexes with VIEW SERVER STATE and/or VIEW DATABASE STATE

result set of sys.indexes with db_datareader role