在 SQL Server 中读取索引大小所需的最低权限

有几种方法可以实现你所追求的。

我将向您展示两个，但还有其他方法。

第一个是签署一个程序，并允许其他用户grant execute on object::yourprocedure仅指定使用它。

首先，您需要一个主密钥，我假设您已经拥有它，您可以使用sys.symmetric_keysDMV 检查它，并查找以“##”为前缀的记录。如果不是简单地创建一个数据库主密钥

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

这是非常重要的密钥，请确保使用本指南对其进行备份

现在您将创建一个证书，您将根据该证书创建模块签名用户。请注意，它仅用于唱歌模块，您不能使用它来测试权限等。所以首先要做的是 - 证书：

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

就像主密钥一样，您也需要备份此证书。指导

现在我们正在创建一个具有证书的用户，该用户将拥有所有权限：

CREATE USER UnlockCertifiedProcedures FOR CERTIFICATE SignModules

提供必要的权利有两种方式：

• 成为 db_reader 为exec sp_addrolemember 'db_datareader',UnlockCertifiedProcedures
• 给予适当的权利GRANT VIEW DEFINITION TO UnlockCertifiedProcedures，GRANT VIEW DATABASE STATE TO UnlockCertifiedProcedures

完成此操作后，下一步就是实际签署此过程，因此有权“执行”该过程的任何人都将从证书用户那里继承权利。

ADD SIGNATURE TO Schema.YourProcedure
BY CERTIFICATE SignModules

去测试：

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

第二个选项是创建一个用户并以该指定用户的身份执行过程。

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

创建以该用户身份执行的过程：

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

现在只需将程序的执行权限授予指定用户：

GRANT EXECUTE ON OBJECT::YourProcedure To SomeUser

就像我说的，还有其他方法，包括角色，但这两个可以让你完成工作。请记住，您无法通过指定“执行为”来跟踪执行过程的用户（登录是可能的）。当您迁移数据库或只是将其恢复到其他地方时，创建证书/数据库密钥也可能会令人头疼。

我问 OP 代码是否打算在整个服务器上执行，即在所有数据库或其中一些数据库中执行，但仍然没有答案，所以我想他需要在所有数据库中执行代码：

管理具有大量 SQL Server 2014 数据库的应用程序 我将解决方案提供给所有服务器。

首先，您的登录名应该可以访问所有数据库，并且您通过授予他权限来完成此操作CONNECT ANY DATABASE PERMISSION

然后您的登录名应该在不访问数据的情况下访问对象定义（这是一个权衡：或者您授予对所有表的读取访问权限，或者您授予对所有定义的访问权限，这意味着包括所有模块，但在此没有数据访问权限案例，所以我更喜欢最后一个），这是由VIEW ANY DEFINITION登录给出的

这两个权限：

• CONNECT ANY DATABASE PERMISSION

• VIEW ANY DEFINITION

  授予在服务器级别登录是找出服务器上所有索引的所有大小的最小权限集，只需更改您的代码以使用sys.allocation_units而不是sys.dm_db_partition_stats

但是，如果您坚持在每个数据库中都使用sys.dm_db_partition_stats它的代码，并且您通过仅授予 1 次登录权限来授予它VIEW DATABASE STATEVIEW SERVER STATE

............................. 回复这个：

如果用户对任何表没有权限，则 sys.indexes 不包含任何行...

我的用户在数据库l中没有SELECT权限，但它“看到”了数据库中的所有表（以及其他对象），并且在无法读取任何数据时对 sys.indexes 没有问题；他在那个数据库里只有VIEW DEFINITION权限

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

仅针对某些数据库的解决方案：

由于 OP 更新了问题，说他对另一个代码开放，并且登录已经被授予访问数据库的权限，我建议只向有问题的用户授予VIEW DEFINITION数据库权限，并使用sys.allocation_units这样的索引大小来获取：

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;

这是描述查看定义权限的technet文章的链接

该VIEW DEFINITION权限允许用户查看授予权限的安全对象的元数据。但是，VIEW DEFINITION 权限并不授予对安全对象本身的访问权限。例如，仅被授予VIEW DEFINITION表权限的用户可以在 sys.objects 目录视图中查看与表相关的元数据。但是，如果没有额外的权限（例如SELECT或 CONTROL），用户将无法从表中读取数据。有关查看元数据的详细信息，请参阅GRANT(Transact-SQL)。

在我们的例子中，我们授予VIEW DEFINITION数据库，因此授予的用户无法访问数据本身，但可以访问所有元数据：

数据库范围

VIEW DEFINITION在此范围内授予有效地否定了指定数据库中被授予者的基于权限的元数据访问。这意味着被授权者可以看到执行 GRANT 语句的数据库上下文中的所有元数据，除非被授权者被拒绝VIEW DEFINITION或CONTROL在模式范围内或对于单个实体（例如表）具有权限。有关在此范围内用于此权限的语法的信息，请参阅GRANT(Transact-SQL)。

据我所知，在这种情况下，所需的最小权限集解决方案是创建一个在模拟上下文中执行的存储过程。通过以下方式执行此操作：

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

见：（https://learn.microsoft.com/en-us/sql/t-sql/statements/execute-as-clause-transact-sql）

然后授予用户执行该存储过程。这将允许用户在没有额外权限的情况下查看该信息（除了 SP 上的 EXECUTE）。

去测试：

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

通过这样做，存储过程将以指定用户或（如果设置为 OWNER）存储过程的创建者/所有者的权限执行，而不是调用 EXECUTE 的用户的权限（这是默认行为，即为什么 sys.indexes 在您的情况下通过选择权限进行过滤）。

视图的基础对象sys.indexes如下：

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

为了检索sys.indexes视图的数据，您必须对GRANT SELECT ON <object> TO <user>上述所有对象进行检索。遗憾的是，由于以下限制，这是不可能的：

系统基表仅在 SQL Server 数据库引擎中使用，不供一般客户使用。它们可能会发生变化，并且不保证兼容性。

参考：系统基表

为每个数据库分配具有db_datareader角色的相关用户可能会更容易，以便检索相关信息。

根据我今天早上在 SQL Server 实例上使用 Windows 登录进行的测试，拥有VIEW SERVER STATE或VIEW DATABASE STATE检索 sys.indexes 信息是不够的。

但是，应用db_datareader数据库角色足以允许访问 sys.indexes 视图。

带有 VIEW SERVER STATE 和/或 VIEW DATABASE STATE 的 sys.indexes 结果集

具有 db_datareader 角色的 sys.indexes 结果集