locked
OLE DB error: OLE DB or ODBC error: The SELECT permission was denied on the object 'handdetail', database 'poker', schema 'dbo'.; 42000. RRS feed

  • Question

  • Hi,

    I recently had a hard disk fail on me and have copied a database and a analysis services solution to anew installation of sql server 2008R2.

    The database is fine and I have opened up my solution fine on my new machine. I can connect to the datasource ok, but whe I come to process the cube I get the error:

    OLE DB error: OLE DB or ODBC error: The SELECT permission was denied on the object 'handdetail', database 'poker', schema 'dbo'.; 42000.

    This worked absolutely fine on my old windows installation.

    I've googled this and found what would seem to be the solution to the problem and under localhost -> security -> Logins I right click/properties on NT AUTHORITY\NETWORK SERVICE Under server roles public is checked.  Under user mappings my database 'poker' is selected and under the database role membership for poker I have everything selected.

    Under the database ->Security -> Users I right click/properties on NT AUTHORITY\NETWORK SERVICE I have owned schemas  as db_backupoperator, db_datawriter, db_datreader and role membership has everything selected.

    I'm definitely no DBA but I can't see why I am getting this errror and I've tried all the google answers I can find on this all of which I would have expected to work but none of which have. I didn't have an error like this when I first set up my database and cube.

    Can anyone suggest what else I could try?

    Thanks,

    Dan

    Saturday, September 22, 2012 11:17 AM

Answers

  • Hi Dan,

    It may be caused by that SSAS is running under a different service account but SQL query is running under a different account.

    Try to give NT AUTHORITY\NETWORK SERVICE sysadmin permission.

    localhost -> security -> Logins I right click/properties on NT AUTHORITY\NETWORK SERVICE, make sure sysadmin is checked under server roles.

    TechNet Subscriber Support
    If you are
    TechNet Subscriptionuser and have any feedback on our support quality, please send your feedback here.


    Thanks,
    Maggie


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

    • Marked as answer by peego Tuesday, September 25, 2012 6:13 PM
    Saturday, September 22, 2012 6:47 PM

All replies

  • Under user mappings my database 'poker' is selected and under the database role membership for poker I have everything selected.

    Everything is selected, including db_owner?  The db_owner role will give the user full permissions so, if that is selected, you won't get the SELECT permission error.  It may be that it is not the NT AUTHORITY\NETWORK SERVICE account that is performing the SELECT.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/


    Saturday, September 22, 2012 3:31 PM
  • Yes everything is selected including db owner.  I've given all logins that same permission but it still doesn't work.  I'm struggling with this one now...
    Saturday, September 22, 2012 4:22 PM
  • Hi Dan,

    It may be caused by that SSAS is running under a different service account but SQL query is running under a different account.

    Try to give NT AUTHORITY\NETWORK SERVICE sysadmin permission.

    localhost -> security -> Logins I right click/properties on NT AUTHORITY\NETWORK SERVICE, make sure sysadmin is checked under server roles.

    TechNet Subscriber Support
    If you are
    TechNet Subscriptionuser and have any feedback on our support quality, please send your feedback here.


    Thanks,
    Maggie


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

    • Marked as answer by peego Tuesday, September 25, 2012 6:13 PM
    Saturday, September 22, 2012 6:47 PM
  • Hi Maggie,

    Thanks for the reply - I think this was the case, however in my impatience because I wasn't sure how to change this setting I did a reinstall of sql server 2008R2 as I'd only just installed it it wasn't too onerous and it did solve my problem.

    Thanks,

    Dan

    Tuesday, September 25, 2012 6:15 PM
  • Thanks! I was having the same problem and your suggestion worked!
    Thursday, July 4, 2019 11:23 AM