I am being daft I'm sure - 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

SQL Server Administration (2000)

I am being daft I'm sure

Author

Topic

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2007-08-30 : 10:45:23

...however I can't see what's wrong with this

I have SQL Server 2000 developer edition on my desktop machine and I am trying to connect a SQL Server 2000 elsewhere on the network as a linked server.

For my machine I am using Windows Authentication and all is well. For the remote server I am using SQL Authentication and all is well if I for example connect using Query analyser.

When I create the linked server I am severely limited in what I can see and do - I can only see a list of 36 tables on the whole server, none of which appear to be the user defined ones I was expecting.

On the linked server properties I have the following settings
GENERAL tab
linked to SQL Server
SERVER OPTIONS tab
These are all default values
SECURITY tab
There is only one login defined as follows
local login:- MyDomain\Steve MySurname (this is my windows login id)
impersonate:- not ticked
remote user:- MyRemoteSQLloginName
remote password:- MyRemoteSQLPassword

for the failed login options I have the "Be made using the login's current security context" option selected

The linked server doesn't even show in Query Analyser at all.

Anyone have any clues please?

thanks

steve

-----------

Don't worry head. The computer will do all the thinking from now on.

Kristen
Test

22859 Posts

Posted - 2007-08-30 : 12:38:07

I can never get my head around creating Linked Servers using the GUI stuff. I'll private mail you the script I use to work around this

(Actually, sorry, can't find your Email address, can you mail me via the Forum and I'll attach it back to you)

Kristen

Kristen
Test

22859 Posts

Posted - 2007-08-30 : 12:41:03

Found your email address mate, check your inbox instead!

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-08-31 : 00:18:23

What do you men 'The linked server doesn't even show in Query Analyser at all'? Did you try query linked server? Got any error?

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2007-08-31 : 06:10:51

Thanks to both for responding. Kristen the script is great, many thanks for that.

I understood that the Object Browser in QA would show the tables in the linked server.

I've also realised that some of the queries I tried wouldn't work because not all the tables on the system are owned by dbo! Can anyone think of any circumstances that this would be appropriate in a live environment on SQL Server 2000?

thanks

steve

-----------

Don't worry head. The computer will do all the thinking from now on.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-08-31 : 16:03:48

It depends on how front end accesses tables.

Kristen
Test

22859 Posts

Posted - 2007-09-01 : 05:25:52

"Can anyone think of any circumstances that this would be appropriate in a live environment on SQL Server 2000?"

VIEWs created for each "owner" so that the records they can see are "restricted"

Possibly also for Tables where the data is specific to the User - duplicate tables, each containing data appropriate to that user.

... but I wouldn't do it that way!

Kristen

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2007-09-03 : 04:15:31

Fair points, thanks guys.

I have had another quick look at the database and the only two users in it are members of the dbo role. These 2 users and the dbo user all have rights explicitly assigned. My theory is that it was an inexperienced person who set it up and who looks after it, in which case I need to work out if I can or should do anything about it. (in my new position I'm not sure it is part of my remit)

steve

-----------

Don't worry head. The computer will do all the thinking from now on.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-04 : 15:19:24

might be some shared database for different applications and the object names are the same? In which case they expect cross database queries but went against it so they just created everything on one database to make connections and creation of sql objects easier?

just a guess

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

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2007-09-06 : 08:28:16

Hello Jen

Very nice to see you around again after all this time. I hope you are well.

Thanks for your comments you may be correct. I suppose time will tell

steve

-----------

Don't worry head. The computer will do all the thinking from now on.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-06 : 14:55:55

I always reply sensibly to members I know

and that includes you... been EXTREMELY busy

Sidetrack, your sig is from Homer Simpson! And I love that episode

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources