C# – The SELECT permission was denied on the object ‘Address’, database ‘CNET_85731’, schema ‘dbo’

asp.net-3.5csql-server-2008

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

Best Answer

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.

Related Topic