SharePoint Custom Web Part throws SELECT permission denied on object 'xxx', database 'yyy', owner 'dbo'

Question

Hi,

I have created a Custom Web Part in SharePoint 2007 using VS 2008 on the dev server. It uses the SQL DB. Everything works fine on my server with testing db which I recreated from the backup client gave me.

Now I'm moving it over to the clients' production server. I was able to deploy, install and activate it.

But when I'm trying to add the web part to the page, it throws a dialogbox with "An error occured while executing the command definition. See the inner exception for details".

This is what I see in the LOG file: ... SELECT permission denied on object 'xxx', database 'yyy', owner 'dbo' ...

The Web Part accesses the DB using the escalated priveleges, i.e. using the portal app pool service account. This account has proper rights to access the DB. I don't understand what can cause the problem.

Any help figuring out what causes the error is greatly appreciated!

EDIT:

Code pasted in comment included here to add formatting:

SPSecurity.RunWithElevatedPrivileges(delegate() 
{ 
    using (WAStockBinEntities context = new WAStockBinEntities()) 
    { 
        var userStockBin = (from stockBins in context.T1view_SP_StockBin 
                            where stockBins.ADName == currUserName 
                            select stockBins) .ToList().FirstOrDefault(); 

        StockRoomNumber.Text = userStockBin.Stockroom; 
        BinNumber.Text = userStockBin.BIN; 
    } 
});

Answer 1

SPSecurity.RunWithElevatedPrivileges does not affect the user that LINQ uses to access the database. I would look at the connection string that LINQ is using to access the database and ensure that the user referenced in the connection string has permissions to the database

You can check the connection string that is being used by calling the Connection.ConnectionString property of the data context.

using (WAStockBinEntities context = new WAStockBinEntities()) 
{ 
    return context.Connection.ConnectionString;
}