November 21, 2007 at 11:02 am
I'm pretty new to SQL security so bear with me... We recently upgraded our Accounting software package and moved to a new SQL 2005 server. The custom views for the 4 databases did not get migrated so I've been recreating them as the need arises.
For security I've created a group in Active Directory and a role on each database containing that group. When creating a new view I simply add the SQL role with select permissions to the view.
The problem came up when I was informed of an Access database with linked tables to a couple of the SQL views. Usually easy enough to fix with the Linked Table Manager, however the ODBC connection fails saying it's unable to find the view. I decided to simply recreate the table link... that's when I noticed that despite the same security setup across databases I am only seeing the views in one of them. In other words, I have database AAA, BBB and CCC but I only see the views for AAA.
What did I do wrong?
Thanks
November 30, 2007 at 10:09 am
In the way that I have used ODBC with Access is that I created an ODBC for each database that I wanted to connect to. When you create and ODBC it asks you to specify a database as the default. When you then link through Access all you can see in the link table manager is the objects in the default database for the ODBC you selected.
November 30, 2007 at 10:20 am
if you don't wish to have multiple DSN or the odbc connections to your Access program then you may want to kick this tire;
> create a single odbc on the client side
> try to centralize your views on one DB and pointing them to the right DBs on the same server or even linked servers.
This is just suggestion and normally I would not consider this method although it is a good experiment...
good luck, and multi-DSN works definitely.
fun fact 🙂
/* have you tried running odbcad32 from strat->run, yes it will open the odbc common dialog box *
happy computing
Cheers,
John Esraelo
November 30, 2007 at 10:25 am
ODBCad32
Very cool. Thanks. This will save me time when setting up users ODBC.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply