Linked SQL Servers problem
-
I have several servers. Server 1 is SQL Server 2000, Servers 2 and 3 are SQL Server 2005. I have linked 2 (2005) to 3 (2005) and 1 (2000) to 2 (2005). I have two windows logins that are in the sysadmin role on all servers. Neither login is an owner of a database. Both logins are members of the builtin/administrators group. Login A can connect to server 1 (2000) in mgmt studio and see all the databases on server 2 (2005). Login B connects to server 1 (2000) in mgmt studio but can only see one database on server 2 (2005). But both can connect to server 2 (2005) and see all the databases in the linked server 3. Server 1 (the SS2000 box) has a number of windows logins mapped to the sa, and one sql login mapped to a local login on server 2. When I change the "...be made using this security context" to sa, it instead inserts the local login. I probably didn't explain this very well, but it has given me a migraine. Any idea what's going on here?
My other signature is witty and insightful.
-
I have several servers. Server 1 is SQL Server 2000, Servers 2 and 3 are SQL Server 2005. I have linked 2 (2005) to 3 (2005) and 1 (2000) to 2 (2005). I have two windows logins that are in the sysadmin role on all servers. Neither login is an owner of a database. Both logins are members of the builtin/administrators group. Login A can connect to server 1 (2000) in mgmt studio and see all the databases on server 2 (2005). Login B connects to server 1 (2000) in mgmt studio but can only see one database on server 2 (2005). But both can connect to server 2 (2005) and see all the databases in the linked server 3. Server 1 (the SS2000 box) has a number of windows logins mapped to the sa, and one sql login mapped to a local login on server 2. When I change the "...be made using this security context" to sa, it instead inserts the local login. I probably didn't explain this very well, but it has given me a migraine. Any idea what's going on here?
My other signature is witty and insightful.
goodideadave wrote:
I probably didn't explain this very well
I don't think it is possible to explain your problem clearly. I have a number of cardinal rules I apply when building a solution one of them is absolutely NO linked servers, now I remember why.
Never underestimate the power of human stupidity RAH
-
goodideadave wrote:
I probably didn't explain this very well
I don't think it is possible to explain your problem clearly. I have a number of cardinal rules I apply when building a solution one of them is absolutely NO linked servers, now I remember why.
Never underestimate the power of human stupidity RAH
They are very fond of linked servers here. So how do you include data in tables on server 2 in a join executing on server 1? We've had to spread data out among different servers due to not having enough disk space on any one box. We're maxed out on our drives and can't afford to buy into the mother ship's SAN. Do you replicate the data between servers?
My other signature is witty and insightful.
-
They are very fond of linked servers here. So how do you include data in tables on server 2 in a join executing on server 1? We've had to spread data out among different servers due to not having enough disk space on any one box. We're maxed out on our drives and can't afford to buy into the mother ship's SAN. Do you replicate the data between servers?
My other signature is witty and insightful.
I work for the mothership and disk space is not an issue. Now I understand why you have such a horrible setup. I would think replication would not be a good solution with your space issue. That leaves your solution, linked server. I'd kill the lot and set up 1 id across all environments purely for this requirement. None of them integrated.
Never underestimate the power of human stupidity RAH