O banco de dados com apenas db_datareader mostra propriedades inválidas para um índice

É por design.

Posso entender por que pode ser confuso poder ver metadados parciais. No entanto, a ideia por trás da função de dados db_datareader é conceder ao usuário acesso de leitura aos dados do usuário, bem como às informações da coluna. (você não seria capaz de criar uma instrução SELECT se também não tivesse acesso aos nomes das colunas.)

Portanto, com isso em mente, mostrar apenas a parte da definição do índice que contém as informações da coluna estaria perfeitamente dentro dos limites da permissão do leitor de dados.

A cláusula de índice filtrado, no entanto, é considerada informação não coluna, portanto, você precisa de mais permissão para poder ver esta parte da definição também.

Você precisa da permissão VIEW DEFINITION naquele objeto, ou VIEW ANY DEFINITION para poder ver aquele bit extra de metadados.

Eu acho que seria bom se eles dessem um aviso quando você gerasse um script CREATE "incompleto".

Você mesmo pode testar:

--create a database
CREATE DATABASE [testdb]
go

--change context to new database
USE [testdb]
GO

--create table
CREATE TABLE [testtable] ( col1 int, col2 int)
GO

--create filtered index
CREATE INDEX [fix_testtable_col1] ON [testtable](col1) WHERE col1 is not null
GO

--create a login
CREATE LOGIN [testlogin] WITH   PASSWORD=N'test', 
                                CHECK_EXPIRATION=OFF, 
                                CHECK_POLICY=OFF

--create a user in your database
CREATE USER [testlogin] FOR LOGIN [testlogin]

--add the user to the data reader role
EXEC sp_addrolemember N'db_datareader', N'testlogin'


--Change your execution contect to the new user with just datareader rights.
EXECUTE AS LOGIN='testlogin'

--try and view the filter definition of the index. It will not show the definition.
SELECT  name,
        has_filter, 
        filter_definition 
FROM sys.indexes
WHERE name='fix_testtable_col1'

--go back to being you
REVERT

--try and view the filter definition of the index. It will succeed.
SELECT  name,
        has_filter, 
        filter_definition 
FROM sys.indexes
WHERE name='fix_testtable_col1'

--give the user VIEW DEFINITION rights
GRANT VIEW DEFINITION ON OBJECT::testTable to testlogin

--switch context once again
EXECUTE AS LOGIN='testlogin'

--try and view the filter definition of the index. this time it will work.
SELECT  name,
        has_filter, 
        filter_definition 
FROM sys.indexes
WHERE name='fix_testtable_col1'


--don't forget to switch back to being you.
REVERT

É verdade que um db_datareader não pode confirmar diretamente o índice filtrado onde a cláusula (sys.indexes.filter_definition) e o SSMS não avisará um db_datareader que o script da definição do índice estará incompleto.

No entanto, há uma solução alternativa que um db_datareader pode usar para confirmar indiretamente o filter_definition: especifique uma dica de índice em uma consulta, com uma cláusula where para testar a cobertura pelo índice filtrado.

O MS-SQL lançará um erro se uma coluna ou valor especificado na cláusula where da consulta NÃO for coberto pela cláusula where do índice filtrado (sys.indexes.filter_definition).

Dessa forma, você pode pelo menos verificar se a cláusula where da consulta que você gostaria de usar está coberta pelo índice filtrado. Você não precisa usar a dica de índice na produção, depois de verificá-la dessa maneira.

Por exemplo, se um índice filtrado for criado por um db_owner como este:

CREATE UNIQUE INDEX UQ_ZipData_PrimaryPerZip
 ON dbo.ZipData (ZIP) WHERE IsPrimaryCity = 1

Então, como db_datareader, posso confirmar que esta consulta funciona, então confirmei que IsPrimaryCity = 1está coberta pelo índice:

SELECT COUNT(*) AS Num
  FROM dbo.ZipData WITH (INDEX (NCI_ZipData_Zip))
  WHERE IsPrimaryCity = 1

Também como db_datareader, posso confirmar que esta consulta falha, então confirmei que IsPrimaryCity = 0NÃO é coberto pelo índice:

SELECT COUNT(*) AS Num
  FROM dbo.ZipData WITH (INDEX (NCI_ZipData_Zip))
  WHERE IsPrimaryCity = 0

A mensagem de erro retornada pelo MS-SQL é:

Msg 8622, Level 16, State 1, Line 1
Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.