6

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?

7

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

It is true that a db_datareader cannot directly confirm the filtered index where clause (sys.indexes.filter_definition), and SSMS will not warn a db_datareader that scripting the index definition will be incomplete.

However, there is a workaround that a db_datareader can use to indirectly confirm the filter_definition: specify an index hint in a query, with a where clause to test for coverage by the filtered index.

MS-SQL will throw an error, if a column or value you specify in the query where clause is NOT covered by the filtered index where clause (sys.indexes.filter_definition).

So this way, you can at least verify that the query where clause you would like to use is covered by the filtered index. You do not need to use the index hint in production, after you have verified it this way.

For example, if a filtered index is created by a db_owner like this:

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

Then as a db_datareader, I can confirm that this query works, so I have confirmed that IsPrimaryCity = 1 is covered by the index:

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

Also as a db_datareader, I can confirm that this query fails, so I have confirmed that IsPrimaryCity = 0 is NOT covered by the index:

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

The error message returned by MS-SQL is:

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.