SELECT permission denied on object 'sysdatabases'

amumaugh

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.

Chris Auckland

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?

amumaugh

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.

Chris Auckland

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?

amumaugh

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

Chris Auckland

ah yes, that would make sense.

Thanks for letting me know how you solved the problem.