C# 对象“地址”、数据库“CNET_85731”、架构“dbo”的 SELECT 权限被拒绝
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1196793/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
The SELECT permission was denied on the object 'Address', database 'CNET_85731', schema 'dbo'
提问by Walter Lockhart
I have been working away for the last 7 months on a C# ASP.NET using Visual Studio 2008 and SQL Server 2008.
在过去的 7 个月里，我一直在使用 Visual Studio 2008 和 SQL Server 2008 在 C# ASP.NET 上工作。
Today, I was running part of my application which was previously running and I got the following error:
今天，我正在运行之前运行的应用程序的一部分，但出现以下错误：
The SELECT permission was denied on the object 'Address', database 'CNET_85731', schema 'dbo'.
对象“地址”、数据库“CNET_85731”、架构“dbo”的 SELECT 权限被拒绝。
I walked through my code and discovered that this error was being caused in the following User Control:
我浏览了我的代码，发现此错误是在以下用户控件中引起的：
protected void sdsAddressesList_Selected(object sender, SqlDataSourceStatusEventArgs e)
{
if (e.AffectedRows == 0)
{
ddlAddresses.Items.Insert((0), new ListItem("No Billing Addresses", "0"));
}
}
the SQLDataSource is defined as follows:
SQLDataSource 定义如下：
<asp:SqlDataSource ID="sdsAddressesList" runat="server" OnSelecting="sdsAddressesList_Selecting" OnSelected="sdsAddressesList_Selected"
SelectCommand="SELECT [AddressId], [ZipPostalCode], ZipPostalCode + ' -- ' + Address1 AS CombinedAddress FROM [Address] WHERE ([CustomerID] = @CustomerID AND [IsBillingAddress] = @IsBillingAddress) ORDER BY [ZipPostalCode]"
ConnectionString="<%$ ConnectionStrings:eCoSysConnection %>">
<SelectParameters>
<asp:Parameter Name="CustomerID" Type="Int32" />
<asp:Parameter Name="IsBillingAddress" Type="Boolean" />
</SelectParameters>
</asp:SqlDataSource>
Basically, what the control does is retrieve a list of addresses for the logged on user from the [Address] table and then populate the drop down list ddlAddresses.
基本上，控件所做的是从 [Address] 表中检索登录用户的地址列表，然后填充下拉列表 ddlAddresses。
The Address table has all the same permissions as the rest of the tables in the database. I have around 60 tables and approximately 200 stored procedures all merrily working away doing SELECTs, etc. No problem. Except for this one issue. What on earth is going on? I haven't made any changes to the database or table permissions.
地址表与数据库中的其余表具有所有相同的权限。我有大约 60 个表和大约 200 个存储过程，它们都在愉快地执行 SELECT 等操作。没问题。除了这一个问题。这到底是怎么回事？我没有对数据库或表权限进行任何更改。
Can anyone help me please.
任何人都可以请帮助我。
Regards
问候
Walter
沃尔特
回答by EPiddy
Well I'm not sure what the root cause was for the SELECT permission denied for your db user but if you run this and then it does indeed work again, then somewhere along the line, your SELECT permission was indeed wiped out.
好吧，我不确定您的 db 用户拒绝 SELECT 权限的根本原因是什么，但是如果您运行此命令然后它确实再次起作用，那么在此过程中的某个地方，您的 SELECT 权限确实被抹去了。
GRANT SELECT ON [dbo].[Address] TO [your user name here]
The good news is that permissions don't magically disappear; the bad news is something (either tooling or otherwise) did indeed either remove or revoke permissions.
好消息是权限不会神奇地消失；坏消息是（工具或其他）确实删除或撤销了权限。
I don't think we have enough information to answer your question as to "why" it happened -- although, none of what you posted appears to be the culprit.
我认为我们没有足够的信息来回答您关于“为什么”发生的问题——尽管您发布的内容似乎都不是罪魁祸首。
回答by PostMan
Had a quick google, found this link Link
有一个快速的谷歌，找到了这个链接链接
It suggests running
它建议运行
select object_name(major_id) as object,
user_name(grantee_principal_id) as grantee,
user_name(grantor_principal_id) as grantor,
permission_name,
state_desc
from sys.database_permissions
where major_id = object_id('Users')
and class = 1
On your database to see what permissions exist, as you may have a DENY select
在您的数据库上查看存在哪些权限，因为您可能有一个拒绝选择
Edit
编辑
select object_name(major_id) as object,
user_name(grantee_principal_id) as grantee,
user_name(grantor_principal_id) as grantor,
permission_name,
state_desc
from sys.database_permissions
WHERE state_desc = 'DENY'
Managed to find a running SQL 2k8 box, and ran it, this new query will show all the deny's. Also try taking the WHERE clause out, to see all the permissions on all tables in the currently selected Database
设法找到一个正在运行的 SQL 2k8 框并运行它，这个新查询将显示所有拒绝的。还可以尝试去掉 WHERE 子句，以查看当前所选数据库中所有表的所有权限
回答by sandeep talabathula
As problem states, "The SELECT permission was denied on the object 'Address', database 'CNET_85731', schema 'dbo' ".
正如问题所述，“对象‘地址’、数据库‘CNET_85731’、架构‘dbo’的 SELECT 权限被拒绝”。
I wonder you can quite simply solve this way:
我想知道你可以很简单地解决这个问题：
- Open SQL Server Management studio
- Navigate to the database 'CNET_85731' >> Security >> Users
- Right click on the one which you are using in your code
- And finally, just select 'db_datareader' inside "Database Role membership" section.
- 打开 SQL Server 管理工作室
- 导航到数据库“CNET_85731”>>安全>>用户
- 右键单击您在代码中使用的那个
- 最后，只需在“数据库角色成员身份”部分中选择“ db_datareader”即可。
Now, I hope you should not get this error again.
现在，我希望您不要再收到此错误。
回答by harvey
In your SQL Server Management Studio, right click on your database, then click permission, then select the user, then grant select,edit,update and delete. Source http://go4answers.webhost4life.com/Example/select-permission-denied-object-159536.aspx
在您的 SQL Server Management Studio 中，右键单击您的数据库，然后单击权限，然后选择用户，然后授予选择、编辑、更新和删除权限。来源 http://go4answers.webhost4life.com/Example/select-permission-denied-object-159536.aspx
回答by patricgh
If it gives you that error within SQL server Management Studio then just run the SQL server Management Studio as administrator that should work.
如果它在 SQL server Management Studio 中给你这个错误，那么只需以管理员身份运行 SQL server Management Studio 就可以了。
回答by Mutlu Kaplan
Remove the following part of your connection string;
删除连接字符串的以下部分；
In??tegrated Security=True;
在??tegrated Security=True;
And everything works fine for me.
一切对我来说都很好。
