Filtered index missing when scripting

Question

Filtered index missing when scripting

Davis H

Right there with Babe

Points: 722
More actions
January 21, 2019 at 2:19 pm

#409448

I have a developer who has view definition permissions on the database and is able to see the DDL on everything just fine.
Except when it comes to the filter on indexes with a filter.
Of course, as sa, I can see them fine. But when he scripts the index out, the WHERE clause is missing. If he brings up the properties of the index and selects the filter tab, the box is blank. When viewing all the indexes in SSMS, the index in question shows (Unique, Non-Clustered, Filtered), but alas ...
Again, he can view SP, Views ... script out table definitions ... but the filter does not script.
Thoughts?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

oogibah SSC Eights! Points: 827 More actions · Answer 1

Davis H - Monday, January 21, 2019 2:19 PM
I have a developer who has view definition permissions on the database and is able to see the DDL on everything just fine.
Except when it comes to the filter on indexes with a filter.
Of course, as sa, I can see them fine. But when he scripts the index out, the WHERE clause is missing. If he brings up the properties of the index and selects the filter tab, the box is blank. When viewing all the indexes in SSMS, the index in question shows (Unique, Non-Clustered, Filtered), but alas ...
Again, he can view SP, Views ... script out table definitions ... but the filter does not script.
Thoughts?

I'm curious, if you use t-sql does it give an error of any sort?

SELECT [SchemaName] = s.[Name]

,[TableName] = t.[Name]

,[IndexName] = i.[Name]

,[IndexType] = i.[type_desc]

,[Filter] = i.filter_definition

FROM sys.indexes i

INNER JOIN sys.tables t ON t.object_id = i.object_id

INNER JOIN sys.schemas s ON s.schema_id = t.schema_id

WHERE t.type_desc = N'USER_TABLE'

AND i.has_filter = 1

Davis H Right there with Babe Points: 722 More actions · Answer 2

The filter_definition is NULL, but when I run it, it has the WHERE clause.

I know, crazy

oogibah SSC Eights! Points: 827 More actions · Answer 3

I suppose you could go the round about way of creating a stored procedure where you parameter the table and index name and have it execute as you but .. seems kind of silly to have to do all that.

Davis H Right there with Babe Points: 722 More actions · Answer 4

Yeah, not when the user can script the indexes just fine. Just not the filter.

It is odd.

It's like the filter is part of some slightly elevated object for which an additional permission needs to be granted?

Odd

oogibah SSC Eights! Points: 827 More actions · Answer 5

Yeah for sure.. and I was looking over the chart below.. and don't see anything specific for viewing filters..

Chart of SQL Server Permissions

For a poster sized chart of all Database Engine permissions in pdf format, see https://aka.ms/sql-permissions-poster.

Davis H Right there with Babe Points: 722 More actions · Answer 6

The user is in a security group and all of the users in that group are experiencing the same thing.

Davis H Right there with Babe Points: 722 More actions · Answer 7

Thanks

I have that hanging in my bedroom already.

Yeah ... right.

Maybe I can hang it in my bathroom ... hmmm ...