Database having only db_datareader shows invalid properties for a index

If a SQL server database user has only public permission in server roles and for a particular database only db_datareader and public permission then while trying script index as CREATE To option in SSMS, the script generated will not be correct.

I mean if an index has filtered condition then that filtered condition is not displayed

So for a user who has owner permission script index as CREATE To will show

USE [test]
GO

/****** Object:  Index [filtered]    Script Date: 12/30/2013 18:54:19 ******/
CREATE NONCLUSTERED INDEX [filtered] ON [dbo].[Table_1] 
(
    [b] ASC
)
WHERE ([Table_1].[b] IS NULL)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

For a user who only has db_datareader permission script index as CREATE To will show

USE [test]
GO

/****** Object:  Index [filtered]    Script Date: 12/30/2013 18:55:13 ******/
CREATE NONCLUSTERED INDEX [filtered] ON [dbo].[Table_1] 
(
    [b] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

It would be OK if a user with db_datareader is not allowed to see the script of an index and shows access denied error. But it shows invalid script. Also if index properties is checked then only filtered condition is not shown and other properties of index are shown. Why this happens?

It's by design.

I can understand why it could be confusing to be able to see partial meta data. However, the idea behind the data role db_datareader is to grant a user read access to both user data as well as column information. (you wouldn't be able to create a SELECT statement if you didn't have access to the column names as well.)

So with that in mind, showing only the part of the index definition that contains column info would be perfectly within the boundaries of the datareader permission.

The filtered index clause however is considered non column information so therefore you need more permission to be able to see this part of the definition as well.

You need VIEW DEFINITION permission on that object, or VIEW ANY DEFINITION to be able to see that extra bit of meta data.

I think it would be nice if they would give a warning when you generate a " incomplete" CREATE script.

You can test it yourself:

--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

StackExchange Database Administrators Q#55811, answer score: 7