Cross database query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Cross database query

Author

Topic

Desmag
Starting Member

18 Posts

Posted - 2006-05-20 : 08:29:42

Hi all,

I need to execute this query:

SELECT F.ThreadID, F.Title, F.Date, F.Author, F.Body, F.Visible, U.Username
FROM
[dbForum].[dboForum].[Forum_message_content] F
LEFT JOIN
[dbUsers].[dboUsers].[Users] U
ON F.Author = U.UserID

...which when executed in [dbForum] gives me this error:
Server user 'dboForum is not a valid user in database 'dbUsers'.

but when I run:
EXEC sp_grantdbaccess 'dboUsers', 'dboForum' (on dbUsers)

I get:
User or role 'dboForum' already exists in the current database.

but when I run:
EXEC sp_helpuser
on dbForum all I get is dboForum (the same with dbUsers)

am I missing something out?

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-05-20 : 09:27:57

where are you running the query?

if in dbforum, you need to grant access to dboforum on dbusers
if in dbusers, you need to grant access to dbousers on dbforum

your grantdbaccess is invoking an error because the names you provided are both users..

--------------------
keeping it simple...

Desmag
Starting Member

18 Posts

Posted - 2006-05-20 : 09:55:01

Thanks very much jen!!

Query is working fine now :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources