主页 / dba / 问题 / 55811
Accepted
IT researcher
Asked: 2013-12-31 05:29:21 +0800 CST

只有 db_datareader 的数据库显示索引的无效属性

  • 772

如果 SQL Server 数据库用户仅具有特定数据库的public权限和权限，那么在 SSMS 中尝试选项时，生成的脚本将不正确。server rolesdb_datareaderpublicscript index as CREATE To

我的意思是，如果索引具有过滤条件，则不会显示该过滤条件

所以对于拥有所有者权限的用户script index as CREATE To将显示

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

对于只有db_datareader权限的用户script index as CREATE To将显示

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

如果db_datareader不允许用户查看索引脚本并显示访问被拒绝错误，那也没关系。但它显示无效的脚本。此外，如果选中索引属性，则仅不显示过滤条件，并显示索引的其他属性。为什么会这样？

sql-server sql-server-2008-r2

2 个回答

  1. Best Answer

    这是设计使然。

    我能理解为什么看到部分元数据会让人感到困惑。但是，数据角色 db_datareader 背后的想法是授予用户对用户数据和列信息的读取权限。（如果您也无权访问列名，您将无法创建 SELECT 语句。）

    因此，考虑到这一点，仅显示包含列信息的索引定义部分将完全在数据读取器权限的范围内。

    然而，过滤索引子句被视为非列信息，因此您需要更多权限才能看到这部分定义。

    您需要该对象的 VIEW DEFINITION 权限，或 VIEW ANY DEFINITION 才能看到额外的元数据。

    我认为如果他们在您生成“不完整”的 CREATE 脚本时发出警告会很好。

    你可以自己测试一下：

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

  2. 确实，db_datareader 无法直接确认筛选索引 where 子句 (sys.indexes.filter_definition)，并且 SSMS 不会警告 db_datareader 编写索引定义脚本将不完整。

    但是，db_datareader 可以使用一种变通方法来间接确认 filter_definition：在查询中指定索引提示，并使用 where 子句来测试过滤索引的覆盖范围。

    如果您在查询 where 子句中指定的列或值未被过滤索引 where 子句 (sys.indexes.filter_definition) 覆盖，MS-SQL 将抛出错误。

    因此，通过这种方式，您至少可以验证要使用的查询 where 子句是否包含在过滤索引中。在以这种方式验证之后，您不需要在生产中使用索引提示。

    例如，如果过滤索引是由 db_owner 创建的，如下所示：

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

    然后作为 db_datareader，我可以确认这个查询有效，所以我已经确认它IsPrimaryCity = 1被索引覆盖：

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

    同样作为 db_datareader，我可以确认此查询失败，因此我已确认IsPrimaryCity = 0索引未涵盖：

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

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

相关问题