Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2006
    Posts
    31

    Unanswered: ERROR 229: SELECT permission denied on object 'sysobjects', database ....

    Hi,

    I granted all the rights on the database to the user (db_owner, public, db_datawriter, etc...) However, I didn't grant the "System Administrators" role. I also specifically granted the select on those tables (sysobjects and sysindexes) for the user and checked through ‘sp_helprotect sysobjects’ command whether there are no specific revokes for for that user.

    However, the user is still getting the below error while trying to expand the "Tables" view in the Enterprise Manager.

    ERROR 229: SELECT permission denied on object 'sysobjects', database 'My_test', owner 'dbo', SELECT permission denied on object 'sysindexes', database 'My_test', owner 'dbo'.

    Also, the user claims that he can’t seem to do anything with the database, he can’t view any objects, and, when he goes to Query Analyzer, if he tries to run a SELECT query on a table (that he know that this table exists), he gets this error:



    SELECT permission denied on object 'tblBillingTrans', database 'My_Test', owner 'dbo'.

    Any help would be greatly appreciated!
    Thanks,
    Alla

  2. #2
    Join Date
    Nov 2006
    Posts
    31
    Please disregard this question... I granted the db_denydatareader option...

  3. #3
    Join Date
    Jun 2010
    Posts
    1
    I realize this post is 3 years old, but I just had the same exact problem and was banging my head over it. Finally I came across this post and it solved the problem for me.

    I registered just to say that...hopefully this will help somebody else in the future.


  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Thanks for taking the time to register and comment. The archives are a tremendous store of knowledge. There really aren't all that many new ways to break things, so having access to the old threads is vitally important!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Dec 2010
    Posts
    1
    Quote Originally Posted by JrSysAdmin View Post
    I realize this post is 3 years old, but I just had the same exact problem and was banging my head over it. Finally I came across this post and it solved the problem for me.

    I registered just to say that...hopefully this will help somebody else in the future.

    Yes, you're right, it was also useful in my case!

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by Pat Phelan View Post
    There really aren't all that many new ways to break things
    That is comforting
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  7. #7
    Join Date
    Sep 2011
    Posts
    1

    Thumbs up This issue got me, too

    I had enabled the denydatareader and denydatawriter for an account I set up when adding SQL Authentication to a cloud server database, since we could not get Windows Authentication to work without a domain.

  8. #8
    Join Date
    Dec 2012
    Posts
    1

    I signed up just for this

    I stumbled across this very issue just now and you saved my hide.
    I can't express how thankful I am

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •