Options

SELECT permission denied on object 'sysdatabases'

amumaughamumaugh Posts: 17 Bronze 1
edited January 2, 2012 9:30AM in SQL Prompt Previous Versions
I get the following error when trying to reference my 2000 server from an SSMS query connected to my 2008 R2 server. I know, I shouldn't still have a 2000 server but that's the reality.

SELECT permission denied on object 'sysdatabases', database 'master', owner 'dbo'.

Steps to reproduce:
Connect to SQL2008R2 server. Open query window. Type SQL2000 server name and "." which would usually bring up SQL Prompt window with database names but instead gives me the error.

I can't figure out where the permission error is coming from because I can connect directly to the SQL2000 instance with the same account and run a direct select against master.sysdatabases with no problems.
· Share on Twitter

Comments

  • Options
    Chris AucklandChris Auckland Posts: 769
    Thanks for your post.

    Can you let me know the exact SQL Prompt version you're using? I don't get the same problem with my SQL 2000 instance.

    Do you get teh same problem if you use a sysadmin account?
    Chris
    · Share on Twitter
  • Options
    amumaughamumaugh Posts: 17 Bronze 1
    Version 5.1.4.11

    I was using a sysadmin account when I first encountered the error but tried it with another just to be on the safe side. I get the error with either account.
    · Share on Twitter
  • Options
    Chris AucklandChris Auckland Posts: 769
    Thanks for your reply.

    Try upgrading to 5.2, because that's the version I tested it it without any problems.

    Does this happen will all databases you connect to?
    Chris
    · Share on Twitter
  • Options
    amumaughamumaugh Posts: 17 Bronze 1
    I will upgrade but I just figured it out. I noticed that I got no error when connected directly to the SQL2000 server and not attempting to fully qualify objects. When I attempted to fully qualify when directly connected, I got an error about DATA ACCESS not being configured. I set this option to true and all SQL Prompt errors went away. I then connected to my 2008 instance and got the error again. It then dawned on me that the linked server setup on my 2008 server to point to my 2000 server used a specific login account which is not sysadmin. There was my duh moment. When I looked at the role membership for this user on the master database on my 2000 server, I saw that someone has specifically set denydatareader. Unchecked that role, restarted SSMS and all is wonderful now. Thanks for holding my hand
    · Share on Twitter
  • Options
    Chris AucklandChris Auckland Posts: 769
    ah yes, that would make sense.

    Thanks for letting me know how you solved the problem.
    Chris
    · Share on Twitter
Sign In or Register to comment.