只有 db_datareader 的數據庫顯示索引的無效屬性

如果 SQL Server 數據庫使用者僅具有特定數據庫的public權限和權限，則在嘗試SSMS 中的選項時，生成的腳本將不正確。server roles``db_datareader``public``script 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不允許使用者查看索引腳本並顯示訪問被拒絕錯誤，那將是可以的。但它顯示無效的腳本。此外，如果檢查索引屬性，則僅不顯示過濾條件，並顯示索引的其他屬性。為什麼會發生這種情況？

這是設計使然。

我可以理解為什麼能夠看到部分元數據會讓人感到困惑。但是，數據角色 db_datareader 背後的想法是授予使用者對使用者數據和列資訊的讀取權限。（如果您也無權訪問列名，您將無法創建 SELECT 語句。）

因此，考慮到這一點，僅顯示包含列資訊的索引定義部分將完全在 datareader 權限的範圍內。

然而，過濾後的索引子句被認為是非列資訊，因此您需要更多權限才能看到這部分定義。

您需要對該對象的 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

引用自：https://dba.stackexchange.com/questions/55811