January 30, 2004 at 4:07 am
We are moving from AS/400 DB2 to SqlServer and in that process I've run into a problem.
The setup is like this:
Our applications is client server Windows programs using ODBC to maintain data.
we have a database containing common data and a number of production data databases
The problem:
When a user connects to the SQLServer through ODBC the user should have access to one of the production data databases and the common data database, so that all tables from the 2 databases is accessible.
is this possible
regards
Henrik Holm, JL-Data
January 30, 2004 at 10:12 am
The login has to be granted to access both databases in SQL Server with proper permissions.
January 30, 2004 at 1:53 pm
Using your description the user would need multiple ODBC setups.
Common Data
1 for each Prod DB.
OR
If you are using VB, have the app open a direct connection to each. Avoid ODBC if you can.
OR
If you don't want 2 connections, you should be able to open a connection to the prod DB, and issue a "Select CommonDB..table ......' to get data from the common table. You would for either of these need to grant the user access to each db.
OR
Create views in each prod db referencing the tables in the common DB.
Still need an ODBC setup at each client for each prod db in either of the last 2.
KlK
February 2, 2004 at 1:13 am
Your last possibility sounds usable. I wasn't aware that I could crease a view in one DataBase referring a table in another DataBase. In fact since my programs does all data access using views this sound really neat.
Thanks
Henrik
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply