September 4, 2008 at 5:08 am
I would like to know if there is a simple way to issue a SELECT statement against (on the same server) a 2nd database - but which uses SQL Authentication.
The scenario is that there are a number of databases which need to read common information from one central database.
The users differ between the various databases, and are added / removed by the client applications.
To avoid the virtually impossible task of maintaining a list of users in the central database, which matches an aggregate list of users in all of the other databases - I would like to use a single, SQL authenticated, user for the SELECT statement indicated above.
The idea being that access to the central database would only be possible from the other databases - and that the administration would be simple, with a single SQL login.
Many thanks for any adivce you can offer.
September 4, 2008 at 5:24 am
Hello,
I'm not sure exactly if I understand your question correctly, but I was thinking that may be you could set up the Central DB as if it was a Linked Server and then specifiy the single SQL Login under the "Connections will …be made using this security context".
It seems however that you can't link back to the same Server, so you would then need to put the Central DB in another Instance.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
September 4, 2008 at 5:45 am
Hello,
Many thanks for the prompt response.
Yes, this is the only option that I have come up with. It would seem that it is possible to 'trick' SQL Server into allowing a Linked Server to itself, by using Microsoft OLE DB Provider for SQL Server (as an Other Data Source, Provider Name).
The issue with this, possibly, is performance - i.e. does a Linked Server, albeit on the same server, perform substantially worse than a direct SELECT statement?
September 4, 2008 at 5:53 am
Hello Again,
I don’t have any benchmark figures, but we use (SQL Server) Linked Servers to separate Instances on the same Windows Server, without any performance degradation.
I wonder though if you might take a performance hit, if you used the OLE DB trick.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
September 4, 2008 at 6:15 am
Hello Again,
Another thought - in SQL 2005 there is the "Execute As" Clause which would allow the SPs in you satellite DBs to execute under the context of a single user (in your central DB). I guess though that you are using SQL 2000 and I don’t think this is available prior to SQL 2005.
Do you have any plans to upgrade?
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
September 4, 2008 at 6:15 am
It's useful to know that you don't suffer performance issues across instances on the same server, using Linked Server.
As the MS OLE DB Driver for SQL Server uses SQLOLEDB (recommended in BOL for performance) - hopefully this is the optimised version of the 'trick' ...
Anyway, I feel some testing coming on!
Thanks again for the help and information.
September 4, 2008 at 6:18 am
Sorry, posts crossed.
Yes, it's SQL 2000 - and there are no imminent plans to upgrade.
Once again, useful information (for the future).
Many thanks.
September 4, 2008 at 7:31 am
One more thought …
Are the Client Application's Users Windows or SQL Logins?
If they were Windows Logins, you could consider adding a Local Group on the Windows Server and then assigning this Windows Group to one Login in the Central DB.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply