The SELECT permission was denied on the object 'Address', database 'CNET_85731', schema 'dbo'

24
votes

I have been working away for the last 7 months on a C# ASP.NET using Visual Studio 2008 and SQL Server 2008.

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'.

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:

<asp:SqlDataSource ID="sdsAddressesList" runat="server" OnSelecting="sdsAddressesList_Selecting" OnSelected="sdsAddressesList_Selected"
    SelectCommand="SELECT [AddressId], [ZipPostalCode], ZipPostalCode + '&nbsp;--&nbsp;' + 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.

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.

Can anyone help me please.

Regards

Walter

6
c#sql-server-2008asp.net-3.5
Is the connection string pointing to the correct database (one which has proper permissions setup)? – shahkalpesh
Yes. the connection string in web.config is as follows: <add name="eCoSysConnection" connectionString="Server=POSITIVECODE-UW\SQLSERVER2008STD;Integrated Security=True;Database=CNET_85731" providerName="System.Data.SqlClient"/> I use this same connection string to access the database at all times. – Walter Lockhart
Has anything changed with your windows login that is tied to sql server? – shahkalpesh
If you login to the SQL box via some for of management studio, as SA and/or this user and run the select query manually, what happens? – PostMan
Not that I am aware of shahkalpesh. All other database access is working fine. – Walter Lockhart

6 Answers

61
votes

As problem states, "The SELECT permission was denied on the object 'Address', database 'CNET_85731', schema 'dbo' ".

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.

Now, I hope you should not get this error again.

14
votes

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.

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.

4
votes

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

3
votes

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

0
votes

If it gives you that error within SQL server Management Studio then just run the SQL server Management Studio as administrator that should work.

0
votes

Remove the following part of your connection string;

In‌​tegrated Security=True;

And everything works fine for me.