March 7, 2003 at 9:55 am
Is it possible in SQL Server 2000 to have a table in one database that is shared so it is visible in another database? I know this is possible in Informix, but I want to know if it can be done in SQL Server.
The reason I ask is that I have a bunch of applications on my website that all have different usernames and passwords. What I'd like to do is integrate all of them into a single username and password system so that you only need to change it in one place in order to update all of them. How would I do that? Do I have to use something like this SQL and apply appropriate table/database permissions to the user logging in?
SELECT [globalLogins].[Logins].username,
[globalLogins].[Logins].password
FROM [globalLogins].[Logins], [myApp1].[users].password
WHERE [globalLogins].[Logins].username ='mylogin' AND
[globalLogins].[Logins].password = 'mypassword' AND
[myApp1].[users].username = [globalLogins].[Logins].username AND
[myApp1].[users].password =[globalLogins].[Logins].password;
Do you think that would work, and if not, what would do it?
March 7, 2003 at 10:07 am
You've got the right idea. Here is where I use abbreviations (perhaps too much)
SELECT GL.username, GL.password
FROM [globalLogins].[dbo].[Logins] GL, -- perhaps use (NOLOCK) here
INNER JOIN [myApp1].[dbo].[users] AU -- perhaps use (NOLOCK) here
ON AU.username = GL.username AND AU.password = GL.password
WHERE GL.username = 'mylogin' AND GL.password = 'mypassword'
Guarddata-
March 7, 2003 at 11:04 am
Thanks, I'll have to try that. I'm not exactly a database wiz, but I do know my way around. I've been trying to figure out how to approach this problem for about a month now though. That idea was this morning's epiphany.
March 7, 2003 at 11:56 am
I have also used views for this.
use databaseB
create view TableInA
as
select a.*
from databaseA.dbo.TableA
Steve Jones
March 7, 2003 at 11:59 am
Slick idea. I didn't even think of using a view. Now, I'm not really familiar with views other than in theory, let alone views in SQL Server. Does SQL Server 2k use a virtual or a materialized view and can it be modified?
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply