SQL 对象“sysobjects”、数据库“mssqlsystemresource”、架构“sys”的 SELECT 权限被拒绝

The SELECT permission was denied on the object 'sysobjects', database 'mssqlsystemresource', schema 'sys'

提问by Chris Holmes

SETUP: SQL Server 2005 & DotNetNuke 05.01.02.

设置：SQL Server 2005 和 DotNetNuke 05.01.02。

This started with me trying to install a DNN Module that had "select * from dbo.sysobjects" in it's SQL scripts. That failed with the following error:

这开始于我尝试安装一个 DNN 模块，该模块在其 SQL 脚本中具有“select * from dbo.sysobjects”。失败并出现以下错误：

The SELECT permission was denied on the object 'sysobjects', database 'mssqlsystemresource', schema 'sys'.

对象“sysobjects”、数据库“mssqlsystemresource”、架构“sys”的 SELECT 权限被拒绝。

I logged into the database via SQL Server Management Studio as the DNN user account, and I get the same error when I try and perform a SELECT on the sysobjects view.

我通过 SQL Server Management Studio 作为 DNN 用户帐户登录到数据库，当我尝试在 sysobjects 视图上执行 SELECT 时，我遇到了同样的错误。

I tried to grant the DNN user account explicit SELECT permission to that view. When I check it by going to Security -> Users -> DNNUserLogin-> right-click -> Properties -> Securables and scroll down to find the sys.sysobjects view, it says this user account has explicit permissions for dbo: And the SELECT checkbox is checked. But I still cannot perform a select on the sysobjects view as that DNN user account.

我试图授予 DNN 用户帐户对该视图的显式 SELECT 权限。当我通过转到安全性 -> 用户 -> DNNUserLogin-> 右键单击​​ -> 属性 -> Securables 并向下滚动以找到 sys.sysobjects 视图来检查它时，它说此用户帐户对 dbo 具有显式权限：和 SELECT复选框被选中。但是我仍然无法以该 DNN 用户帐户的身份在 sysobjects 视图上执行选择。

What am I doing wrong? How can I make this work?

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

回答by Chris Holmes

This was a problem with the user having deny privileges as well; in my haste to grant permissions I basically gave the user everything. And deny was killing it. So as soon as I removed those permissions it worked.

这也是具有拒绝权限的用户的问题；在我匆忙授予权限时，我基本上给了用户一切。否认正在扼杀它。因此，一旦我删除了这些权限，它就起作用了。

回答by Raihan

I had the same error and SOLVED by removing the DB roles db_denydatawriterand db_denydatreaderof the DB user. For that, select the appropriate DB user on logins >> properties >> user mappings >> find out DB and select it >> uncheck the mentioned Db user roles. Thats it !!

通过删除数据库角色db_denydatawriter和db_denydatreader数据库用户，我遇到了同样的错误并解决了。为此，在登录时选择适当的数据库用户 >> 属性 >> 用户映射 >> 找出数据库并选择它 >> 取消选中提到的数据库用户角色。就是这样 ！！

回答by Rudy

Execute this code on a good server which will provide you the complete rights for PUBLIC role. Copy the output and paste to the server with the issue. Execute. Try logging in again. It fixed our problem.

在良好的服务器上执行此代码，它将为您提供 PUBLIC 角色的完整权限。复制输出并粘贴到有问题的服务器。执行。再次尝试登录。它解决了我们的问题。

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]

回答by Lowell

Since there are so many possibilities for what might be wrong. Here's another possibility to look at. I ran into something where I had set up my own roles on a database. (For instance, "Administrator", "Manager", "DataEntry", "Customer", each with their own kinds of limitations) The only ones who could use it were "Manager" role or above--because they were also set up as sysadmin because they were adding users to the database (and they were highly trusted). Also, the users that were being added were Windows Domain users--using their domain credentials. (Everyone with access to the database had to be on our domain, but not everyone on the domain had access to the database--and only a few of them had access to change it.)

因为可能出错的可能性有很多。这是另一种可能性。我遇到了在数据库上设置自己的角色的问题。（例如，“Administrator”、“Manager”、“DataEntry”、“Customer”，每个都有自己的限制）可以使用它的只有“Manager”角色或以上角色——因为它们也被设置作为系统管理员，因为他们将用户添加到数据库中（并且他们非常信任）。此外，添加的用户是 Windows 域用户——使用他们的域凭据。（有权访问数据库的每个人都必须在我们的域中，但并非域中的每个人都有权访问数据库——而且只有少数人有权更改它。）

Anyway, this working system suddenly stopped working and I was getting error messages similar to the above. What I ended up doing that solved it was to go through all the permissions for the "public" role in that database and add those permissions to all of the roles that I had created. I know that everyone is supposed to be in the "public" role even though you can't add them (or rather, you can "add" them, but they won't "stay added").

无论如何，这个工作系统突然停止工作，我收到了与上述类似的错误消息。我最终解决的问题是检查该数据库中“公共”角色的所有权限，并将这些权限添加到我创建的所有角色中。我知道每个人都应该处于“公共”角色，即使您无法添加它们（或者更确切地说，您可以“添加”它们，但它们不会“保持添加”）。

So, in "SQL Server Management Studio", I went into my application's database, in other words (my localized names are obscured within <> brackets): " (SQL Server - sa)"\Databases\\Security\Roles\Database Roles\public". Right-click on "public" and select "Properties". In the "Database Role Properties - public" dialog, select the "Securables" page. Go through the list and for each element in the list, come up with an SQL "Grant" statement to grant exactly that permission to another role. So, for instance, there is a scalar function "[dbo].[fn_diagramobjects]" on which the "public" role has "Execute" privilege. So, I added the following line:

因此，在“SQL Server Management Studio”中，我进入了我的应用程序的数据库，换句话说（我的本地化名称在 <> 括号内被掩盖了）：“（SQL Server - sa）”\Databases\\Security\Roles\Database Roles \public”。右键单击“public”并选择“Properties”。在“Database Role Properties - public”对话框中，选择“Securables”页面。浏览列表并为列表中的每个元素提出一个 SQL“Grant”语句来准确地授予另一个角色该权限。因此，例如，有一个标量函数“[dbo].[fn_diagramobjects]”，“public”角色对其具有“执行”特权。所以，我添加了以下行：

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

Once I had done this for all the elements in the "Securables" list, I wrapped that up in a while loop on a cursor selecting through all the roles in my roles table. This explicitly granted all the permissions of the "public" role to my database roles. At that point, all my users were working again (even after I removed their "sysadmin" access--done as a temporary measure while I figured out what happened.)

对“Securables”列表中的所有元素完成此操作后，我将其包装在一个 while 循环中，在一个光标上选择角色表中的所有角色。这将“公共”角色的所有权限显式授予我的数据库角色。那时，我的所有用户都又开始工作了（即使在我删除了他们的“系统管理员”访问权限之后——这是在我弄清楚发生了什么时作为临时措施完成的。）

I'm sure there's a better (more elegant) way to do this by doing some kind of a query on the database objects and selecting on the public role, but after about half and hour of investigating, I wasn't figuring it out, so I just did it the brute-force method. In case it helps someone else, here's my code.

我确信有一种更好的（更优雅的）方法可以通过对数据库对象进行某种查询并选择公共角色来做到这一点，但是经过大约半小时的调查，我没有弄清楚，所以我只是用蛮力方法来做的。如果它对其他人有帮助，这是我的代码。

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

Once that is in the system, I just needed to "Exec GrantAccess" to make it work. (Of course, I have a table [RoleList] which contains a "AppRoleName" field that contains the names of the database roles.

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

So, the mystery remains: why did all my users lose their "public" role and why could I not give it back to them? Was this part of an update to SQL Server 2008 R2? Was it because I ran another script to delete each user and add them back so to refresh their connection with the domain? Well, this solves the issue for now.

所以，谜团仍然存在：为什么我的所有用户都失去了他们的“公共”角色，为什么我不能把它还给他们？这是 SQL Server 2008 R2 更新的一部分吗？是不是因为我运行了另一个脚本来删除每个用户并将它们添加回来以便刷新他们与域的连接？嗯，这暂时解决了这个问题。

One last warning: you probably should check the "public" role on your system before running this to make sure there isn't something missing or wrong, here. It's always possible something is different about your system.

最后一个警告：您可能应该在运行此之前检查系统上的“公共”角色，以确保没有丢失或错误的东西，在这里。您的系统总是可能有所不同。

Hope this helps someone else.

希望这对其他人有帮助。

回答by Senthil

It looks like someone might have revoked the permissions on sys.configurationsfor the public role. Or denied access to this view to this particular user. Or the user has been created after the public role was removed from the sys.configurationstables.

看起来有人可能撤销sys.configurations了公共角色的权限。或者拒绝此特定用户访问此视图。或者在从sys.configurations表中删除公共角色后创建了用户。

Provide SELECTpermission to public user sys.configurationsobject.

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

回答by Chamod Pathirana

I solved this by referring properties of login user under the security, logins. then go to User Mapping and select the database then check db_datareaderand db_dataweriteroptions.

我通过在安全性、登录名下引用登录用户的属性来解决这个问题。然后转到用户映射并选择数据库，然后检查db_datareader和db_dataweriter选项。

回答by mohas

In my case, my site was hosted on shared hosting and there was a resource over usage not even relating to my database, thus my database was locked down, the hosting panel was Plesk

就我而言，我的网站托管在共享主机上，并且有一个资源过度使用甚至与我的数据库无关，因此我的数据库被锁定，托管面板是 Plesk