Sql-docs: Document that sys.indexes omits filter definitions even when user has select permission

[EamonNerbonne]

The filter_definition is specified to be "NULL for heap or non-filtered index". Additionally, sys.indexes has the caveat that "The visibility of the metadata in catalog views is limited to securables that a user either owns or on which the user has been granted some permission."

However, when the user has been granted select permission on a table (but not insert), the indexes on that table are visible, except that the filter_definition is null even when the index is filtered.

I'm not sure if that's a bug; but it's certainly surprising; expanding the filter_definition column specification ala "NULL for heap or non-filtered index or when the user has insufficient permissions on the table" would better hint at the actual behavior.

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: 78d65932-6ae2-4a7d-6f5b-063a744c476b
• Version Independent ID: 3a20a4f3-b7f8-a6b1-a4d8-bdcc95ce45b4
• Content: sys.indexes (Transact-SQL) - SQL Server
• Content Source: docs/relational-databases/system-catalog-views/sys-indexes-transact-sql.md
• Product: sql
• Technology: system-objects
• GitHub Login: @stevestein
• Microsoft Alias: sstein

[MightyPen]

@stevestein Hi Steve, Maybe a few tests with a simple table, simple nonclustered index, and a simple non-DBO user, could reveal what Permissions the user needs on the table?

@EamonNerbonne Hello Eamon, Processing has begun for your Issue #2830 .

cc: @msebolt

[rothja]

@EamonNerbonne Thank you for this feedback, and my apologies that it has taken so long to respond. @stevestein is out of the office for an extended period. I can see how that is confusing, and I too am not sure if this is a bug or just not explained well in the docs. I will try to run some tests soon to see if I can repo so I know how to change this in the docs. #reassign:rothja

[rothja]

@EamonNerbonne Again, sorry for the long delay. I tested this today and found the behavior to be as you describe. I also found that if the user had VIEW DEFINITION permission in addition to select permission on the table, that they can see the filter definition. I'll make a change in the article to address what you found here. Thank you.