Permissões mínimas necessárias para ler o tamanho do índice no SQL Server

Existem algumas maneiras de alcançar o que você está procurando.

Vou mostrar-lhe dois, mas há outras maneiras também.

A primeira é assinar um procedimento e permitir que outros usuários o usem grant execute on object::yourprocedureapenas especificando.

Antes de tudo você precisa de uma chave mestra, que eu suponho que você já tenha, e você pode verificá-la usando sys.symmetric_keysDMV e procurando por registro que tenha '##' como prefixo. Se não, basta criar uma chave mestra de banco de dados

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

Esta é uma chave muito importante, e certifique-se de fazer backup usando este guia

Agora você criará um certificado com base no qual criará o usuário de assinatura do módulo. Observe que é apenas para módulos de canto e você não pode usá-lo para testar privilégios etc.

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

Assim como a chave mestra, você também precisa fazer backup desse certificado. guia

Agora estamos criando um usuário com certificado que terá todos os direitos:

CREATE USER UnlockCertifiedProcedures FOR CERTIFICATE SignModules

Existem duas maneiras de fornecer os direitos necessários:

• tornando-se um db_reader comoexec sp_addrolemember 'db_datareader',UnlockCertifiedProcedures
• dando direitos apropriados GRANT VIEW DEFINITION TO UnlockCertifiedProcedures,GRANT VIEW DATABASE STATE TO UnlockCertifiedProcedures

Feito isso, o próximo passo é realmente assinar este procedimento, então quem tiver direitos para 'executar' o procedimento herdará os direitos do usuário do certificado.

ADD SIGNATURE TO Schema.YourProcedure
BY CERTIFICATE SignModules

Testar:

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

A segunda opção é criar um usuário e executar o procedimento como aquele usuário especificado.

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

Crie o procedimento executando como esse usuário:

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

Agora simplesmente dê direitos de execução em um procedimento para um usuário especificado:

GRANT EXECUTE ON OBJECT::YourProcedure To SomeUser

Como eu disse, existem outras maneiras, incluindo papéis, mas esses dois podem fazer um trabalho. Apenas lembre-se de que você não pode rastrear um usuário que executou o procedimento especificando 'executar como' (o login é possível). Também criar um certificado/chaves de banco de dados pode ser uma dor de cabeça quando você está migrando o banco de dados ou simplesmente restaurando-o em outro lugar.

Perguntei ao OP se o código se destina a ser executado em todo o servidor, ou seja, em todos os bancos de dados ou em alguns deles e ainda sem resposta, então suponho que ele precise que o código seja executado em TODOS os bancos de dados:

gerenciando uma aplicação com um grande número de bancos de dados SQL Server 2014 dou a solução para todo o servidor.

Primeiro, seu login deve acessar todos os bancos de dados, e você faz isso concedendo a eleCONNECT ANY DATABASE PERMISSION

Então seu login deve acessar as definições de objetos sem acessar os dados (aqui está uma troca: ou você dá acesso de leitura em todas as suas tabelas, ou você dá acesso a todas as definições, isso significa todos os módulos incluídos, mas nenhum acesso a dados neste caso, então eu prefiro o último), isso é dado por VIEW ANY DEFINITIONlogin

Essas duas permissões:

• CONNECT ANY DATABASE PERMISSION

• VIEW ANY DEFINITION

  concedido ao login no nível do servidor é o conjunto mínimo de permissões para descobrir todos os tamanhos de todos os índices no servidor, basta alterar seu código para usar sys.allocation_unitsem vez desys.dm_db_partition_stats

Mas se você insistir em que seu código use sys.dm_db_partition_statsele requer VIEW DATABASE STATEem todos os bancos de dados, e você o concede concedendo apenas 1 vez VIEW SERVER STATEpermissão ao seu login

......................................... Em resposta a esta:

sys.indexes não contém linhas se o usuário não tiver permissões em nenhuma tabela...

Meu usuário nãol tem permissão em um banco de dados , mas ele "vê" todas as tabelas (e outros objetos também) em um banco de dados, e não tem nenhum problema com sys.indexes enquanto ele não consegue ler nenhum dado; tudo o que ele tem nesse banco de dados é permissãoSELECTVIEW DEFINITION

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

Solução para apenas alguns bancos de dados:

Como o OP atualizou a pergunta dizendo que ele está aberto a outro código e o login já tem acesso ao banco de dados, sugiro conceder VIEW DEFINITIONpermissão apenas no banco de dados ao usuário em questão e usar sys.allocation_unitspara obter tamanhos de índice como este:

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;

E aqui está o link para o artigo technet descrevendo a permissão VIEW DEFINITION

A VIEW DEFINITIONpermissão permite que um usuário veja os metadados do protegível no qual a permissão foi concedida. No entanto, a permissão VIEW DEFINITION não confere acesso ao protegível em si . Por exemplo, um usuário que recebe VIEW DEFINITIONpermissão apenas em uma tabela pode ver os metadados relacionados à tabela na exibição do catálogo sys.objects. No entanto, sem permissões adicionais, como SELECTou CONTROL, o usuário não pode ler dados da tabela. Para obter mais informações sobre como exibir metadados, consulte GRANT(Transact-SQL).

No nosso caso, concedemos VIEW DEFINITIONem um banco de dados, portanto, o usuário concedido não tem acesso aos dados em si, mas a todos os metadados:

Escopo do banco de dados

VIEW DEFINITIONconcedido neste escopo nega efetivamente o acesso de metadados baseado em permissões para o beneficiário no banco de dados especificado. Isso significa que o beneficiário pode ver todos os metadados no contexto do banco de dados no qual a instrução GRANT é executada , a menos que o beneficiário seja negado VIEW DEFINITIONou CONTROLpermissões no escopo do esquema ou para uma entidade individual, como uma tabela. Para obter informações sobre a sintaxe a ser usada para essa permissão nesse escopo, consulte GRANT(Transact-SQL).

Até onde eu sei, a solução mínima necessária de conjunto de permissões nesse caso é criar um procedimento de armazenamento que é executado dentro de um contexto representado. Faça isso por:

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

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

Em seguida, conceda aos usuários a execução nesse procedimento armazenado. Isso permitirá que o usuário visualize essas informações sem permissões adicionais (além de EXECUTE no SP).

testar:

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

Ao fazer isso, o procedimento armazenado será executado com as permissões do usuário designado ou (se definido como OWNER) do criador/proprietário do procedimento armazenado, em vez das permissões do usuário que está chamando EXECUTE (este é o comportamento padrão, que é por que sys.indexes está filtrando por permissões selecionadas no seu caso).

Os objetos subjacentes para a sys.indexesexibição são os seguintes:

• sys.sysidxstats
• sys.syssingleobjrefs
• valores de sys.syspal
• sys.sysschobjs

Para recuperar dados para a sys.indexesexibição, você precisaria GRANT SELECT ON <object> TO <user>para todos os objetos acima. Infelizmente, isso não é possível, devido às seguintes restrições:

As tabelas base do sistema são usadas apenas no Mecanismo de Banco de Dados do SQL Server e não são para uso geral do cliente. Eles estão sujeitos a alterações e a compatibilidade não é garantida.

Referência: Tabelas Base do Sistema

Pode ser mais fácil atribuir ao usuário em questão a função db_datareader para cada banco de dados para recuperar as informações relevantes.

Do meu teste esta manhã com um Windows Login em uma instância do SQL Server, não é suficiente ter VIEW SERVER STATEou VIEW DATABASE STATErecuperar as informações de sys.indexes.

No entanto, aplicar a db_datareaderfunção de banco de dados é suficiente para permitir o acesso à exibição sys.indexes.

conjunto de resultados de sys.indexes com VIEW SERVER STATE e/ou VIEW DATABASE STATE

conjunto de resultados de sys.indexes com função db_datareader