none
SELECT Permission was denied on the object

    Question

  • Hello. I am a IT-Supporter trainee at a small company and I have gotten to work with our SQL database, and having to upgrade a service program, when I do so it ask me if i want to run a SQL-Script to update our database, i found this subsucios and decided not to, leaving the program unable to login again.

    I decided to simply make a backup of our datapase on our SQL and restore it on our Test server, how ever i keep getting "The SELECT permission was denied on the object 'table name', database 'DB name', schema 'dbo' (Error:229)" I have been looking a little around but was unable to find anything which solved my problem. I have checked all of the rights on the user I am trying to log in with to rule out that.

     

    Please keep in mind I am still a trainee in my first ½ year at the job.

    Tuesday, December 21, 2010 9:19 AM

Answers

  • To grant freddie access on tables in a schema:

       GRANT SELECT, UPDATE, DELETE, INSERT on SCHEMA::dbo TO freddie

    Do you get the error while running RESTORE command???


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by Hitsuchan Tuesday, December 21, 2010 10:29 AM
    Tuesday, December 21, 2010 10:17 AM

All replies

  • This normally means you don't have necessary permission on that object. Did your account has at-least data_reader permission on that table ? If not, you should get in touch with your DBA to grant your account with the minimum required permission.

    Hope, this may help.


    SKG: Please Marked as Answered, if it resolves your issue.
    Tuesday, December 21, 2010 9:39 AM
  • I have our Database Adminstrator account. and yes I made sure to give him all of the necessary permissions, db_datareader and all of the other things. The funny thing is that the exatly same setup works on our other SQL server which we normaly use, its just when i move it to the Test server it wont function, I have checked all of the name that might need to be checked, but I am unable to find it
    Tuesday, December 21, 2010 10:03 AM
  • To grant freddie access on tables in a schema:

       GRANT SELECT, UPDATE, DELETE, INSERT on SCHEMA::dbo TO freddie

    Do you get the error while running RESTORE command???


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by Hitsuchan Tuesday, December 21, 2010 10:29 AM
    Tuesday, December 21, 2010 10:17 AM
  • No I did not, it just ran the restore and said the restore was complet

     

    I'll try run that  command though i did try to run

    Grant select on dbo.mytablename to public

    but i'll try yours


    Edit:;:;

    I tried to run yours and its working now :) Thanks alot!

    Tuesday, December 21, 2010 10:24 AM
  • The other probability is that the user account is not getting mapped properly.

    Dropped the existing user account from the database (YourDatabase --> Security --> User).

    Then re-create the user account and assign it the required permissions. Hope, this may help.


    SKG: Please Marked as Answered, if it resolves your issue.
    Tuesday, December 21, 2010 10:27 AM