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

Question

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

Answer 1

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.