该SELECT权限被拒绝的对象“系统对象”数据库“mssqlsystemresource”，架构“S

设置：SQL Server 2005中与DotNetNuke的02年5月1日。

这开始跟我试图安装一个DNN模块，它已经“选择dbo.sysobjects *”在它的SQL脚本。这失败，出现以下错误：

该SELECT权限被拒绝的对象“系统对象”数据库“mssqlsystemresource”，架构“SYS”。

我登录到通过SQL Server Management Studio中的数据库作为DNN用户帐户，我也得到了同样的错误，当我试图在系统对象视图执行SELECT。

我想明确的SELECT权限授予DNN用户帐户这一观点。 >用户 - - > DNNUserLogin->右键 - >属性 - 当我被转到安全检查>安全对象和向下滚动找到sys.sysobjects查看，它说，该用户帐户具有DBO明确权限：和选择复选框被选中。但我仍然无法对系统对象视图，该DNN用户帐户进行选择。

我究竟做错了什么？我怎样才能使这项工作？

Answer 1:

这是与具有否认特权以及用户的一个问题; 我匆忙授予权限我基本放弃用户的一切内容。并拒绝被杀死它。所以，当我删除了这些权限，它的工作。

Answer 2:

我有同样的错误，并通过删除数据库角色解决db_denydatawriter和db_denydatreader数据库用户。为此，选择登录相应的DB用户>>性能>>用户映射>>找出数据库，并选择它>>取消所提到的数据库用户的角色。而已！！

Answer 3:

一个好的服务器，将为您提供公共角色的完整权利上执行该代码。复制输出并粘贴到该问题的服务器。执行。请尝试重新登录。它固定我们的问题。

SELECT  SDP.state_desc ,
        SDP.permission_name ,
        SSU.[name] AS "Schema" ,
        SSO.[name] ,
        SSO.[type]
FROM    sys.sysobjects SSO
        INNER JOIN sys.database_permissions SDP ON SSO.id = SDP.major_id
        INNER JOIN sys.sysusers SSU ON SSO.uid = SSU.uid
ORDER BY SSU.[name] ,
        SSO.[name]

Answer 4:

因为有什么可能是错误的这么多的可能性。这里的另一种可能性来看待。我遇到了一些我在那里建立自己的角色的数据库上。（例如，“管理员”，“经理人”，“的DataEntry”，“客户”，每个都有自己的各种各样的限制）的唯一谁可以使用它是“经理人”角色或以上 - 因为他们还成立了作为系统管理员，因为他们将用户添加到数据库中（和他们高度信任）。此外，正在添加的用户是Windows域用户 - 使用他们的域凭据。（每个人都可以访问该数据库必须是对我们的域名，但域名不是每个人都必须对数据库的访问 - 只有少数人得以进入修改）

不管怎么说，这个工作系统突然停止工作，我得到类似上面的错误消息。我最后做的是解决它是要经过所有的权限在该数据库中的“公共”的作用，这些权限添加到所有我已创建的角色的是什么。我知道每个人都应该是在“公众”的角色，即使你不能在其中添加（或者说，你可以在“添加”，但他们不会“留加”）。

因此，在“SQL Server Management Studio中”，我走进我的应用程序的数据库，换句话说，（我的本地化名称在<>括号遮蔽）：“（SQL服务器 - SA）” \数据库\\安全\角色\数据库角色\公众公开”上单击鼠标右键。‘’并选择‘属性数据库角色属性’在。‘ - 市民’对话框中，选择‘安全对象’页面列表并在列表中的每个元素去，拿出一个SQL“格兰特”语句正是权限授予另一个角色。所以，举例来说，有一个标量函数“[DBO]。[fn_diagramobjects]”上的“公共”角色“执行”权限。所以，我加入下列行：

EXEC ( 'GRANT EXECUTE ON [dbo].[fn_diagramobjects] TO [' + @RoleName + '];' )

有一次，我已经做到了这一点在“安全对象”列表中的所有元素，我包，最多在光标通过我的角色表中的所有角色中选择一个while循环。这明确授予的“公众”的角色所有的权限，以我的数据库角色。在这一点上，我所有的用户再次工作（即使我消除了他们的“系统管理员”访问 - 做过作为一项临时措施，而我想通了什么事）

我敢肯定有一个更好的（更优雅）的方式做某种对数据库对象的查询和选择的公共角色要做到这一点，但经过调查约半小时，我是不是想出来的，所以我只是做了它的穷举法。在情况下，它可以帮助别人，这是我的代码。

CREATE PROCEDURE [dbo].[GrantAccess]
AS
DECLARE @AppRoleName AS sysname

DECLARE AppRoleCursor CURSOR LOCAL SCROLL_LOCKS FOR
    SELECT AppRoleName FROM [dbo].[RoleList];

OPEN AppRoleCursor

FETCH NEXT FROM AppRoleCursor INTO @AppRoleName
WHILE @@FETCH_STATUS = 0
BEGIN

    EXEC ( 'GRANT EXECUTE ON [dbo].[fn_diagramobjects] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT EXECUTE ON [dbo].[sp_alterdiagram] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT EXECUTE ON [dbo].[sp_creatediagram] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT EXECUTE ON [dbo].[sp_dropdiagram] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT EXECUTE ON [dbo].[sp_helpdiagramdefinition] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT EXECUTE ON [dbo].[sp_helpdiagrams] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT EXECUTE ON [dbo].[sp_renamediagram] TO [' + @AppRoleName + '];' ) 

    EXEC ( 'GRANT SELECT ON [sys].[all_columns] TO [' + @AppRoleName + '];' )
    EXEC ( 'GRANT SELECT ON [sys].[all_objects] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[all_parameters] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[all_sql_modules] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[all_views] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[allocation_units] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[assemblies] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[assembly_files] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[assembly_modules] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[assembly_references] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[assembly_types] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[asymmetric_keys] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[certificates] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[change_tracking_tables] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[check_constraints] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[column_type_usages] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[column_xml_schema_collection_usages] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[columns] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[computed_columns] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[conversation_endpoints] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[conversation_groups] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[conversation_priorities] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[crypt_properties] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[data_spaces] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[database_audit_specification_details] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[database_audit_specifications] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[database_files] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[database_permissions] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[database_principal_aliases] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[database_principals] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[database_role_members] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[default_constraints] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[destination_data_spaces] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[event_notifications] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[events] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[extended_procedures] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[extended_properties] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[filegroups] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[foreign_key_columns] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[foreign_keys] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[fulltext_catalogs] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[fulltext_index_catalog_usages] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[fulltext_index_columns] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[fulltext_index_fragments] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[fulltext_indexes] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[fulltext_stoplists] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[fulltext_stopwords] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[function_order_columns] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[identity_columns] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[index_columns] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[indexes] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[internal_tables] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[key_constraints] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[key_encryptions] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[message_type_xml_schema_collection_usages] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[module_assembly_usages] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[numbered_procedure_parameters] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[numbered_procedures] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[objects] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[parameter_type_usages] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[parameter_xml_schema_collection_usages] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[parameters] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[partition_functions] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[partition_parameters] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[partition_range_values] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[partition_schemes] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[partitions] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[plan_guides] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[procedures] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[remote_service_bindings] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[routes] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[schemas] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[service_contract_message_usages] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[service_contract_usages] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[service_contracts] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[service_message_types] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[service_queue_usages] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[service_queues] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[services] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[spatial_index_tessellations] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[spatial_indexes] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[sql_dependencies] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[sql_modules] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[stats] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[stats_columns] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[symmetric_keys] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[synonyms] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[syscolumns] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[syscomments] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[sysconstraints] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[sysdepends] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[sysfilegroups] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[sysfiles] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[sysforeignkeys] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[sysfulltextcatalogs] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[sysindexes] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[sysindexkeys] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[sysmembers] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[sysobjects] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[syspermissions] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[sysprotects] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[sysreferences] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[system_columns] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[system_objects] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[system_parameters] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[system_sql_modules] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[system_views] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[systypes] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[sysusers] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[table_types] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[tables] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[transmission_queue] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[trigger_events] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[triggers] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[type_assembly_usages] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[types] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[views] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[xml_indexes] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[xml_schema_attributes] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[xml_schema_collections] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[xml_schema_component_placements] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[xml_schema_components] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[xml_schema_elements] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[xml_schema_facets] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[xml_schema_model_groups] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[xml_schema_namespaces] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[xml_schema_types] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[xml_schema_wildcard_namespaces] TO [' + @AppRoleName + '];' ) 
    EXEC ( 'GRANT SELECT ON [sys].[xml_schema_wildcards] TO [' + @AppRoleName + '];' ) 

    FETCH NEXT FROM AppRoleCursor INTO @AppRoleName
END

CLOSE AppRoleCursor
RETURN 0

GO

一旦在系统中，我只需要“Exec的多个GrantAccess”，使其工作。（当然，我有一个表[RoleList的]它包含了包含数据库角色的名称是“AppRoleName”字段。

那么，神秘的仍然是：为什么我的所有用户都失去了“公众”的角色，我为什么不能还给他们呢？是一个更新到SQL Server 2008 R2的一部分？是不是因为我跑了另一个脚本，删除每个用户并添加他们回来，刷新其与域连接？那么，这对于解决现在的问题。

最后一个警告：你应该运行此，以确保没有东西丢失或错误，在此之前，请检查您的系统上的“公众”的角色。它总是可能的事情是关于你的系统不同。

希望这可以帮助别人。

Answer 5:

它看起来像一个人可能已经撤销的权限sys.configurations的公共角色。或拒绝访问该视图这个特定的用户。或公共角色从去除之后，该用户已创建sys.configurations表。

提供SELECT权限公共用户sys.configurations对象。

Answer 6:

我通过在安全，登录指登录用户的特性解决了这个。然后转到用户映射和选择，然后检查数据库的db_datareader和db_dataweriter选项。