Since when does ODBC connection require SA rights

[divinyl]

Hi all

It's weird... ive created a test SQL 2000 database, defined a sql login with datareader rights to it, configured an odbc connection to point to it, selected SQL authentication and typed in my sql login - when it comes to the screen in the odbc configuration to select a database, i cannot see my database. If I make my sql login a dbowner, still cant see it. If i make my login a sysadmin, then i can see it! What's that all about??

Any suggestions?

Thanks,
Div

 

[fuzzyocelot]

I could be wrong, but it sounds like the user account isn't sync'd up with the login. Try running the following code in Query Analyzer and see if it returns your login.

use yourdatabasenamehere
go

sp_change_users_login report

 

[divinyl]

Thanks for replying. Unfortuantely that's not it. The login is definitely associated with the user. I created the db, created the login and gave it db data reader rights to the database. In that order. It's weird...

Could there be something wrong with m ODBC drivers perhaps?

 

[ptheriault]

What's the default db of your user account? It sounds to me like the login doesn't have permissions to the db.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[divinyl]

I have made the default db for the user the test database in question. I can connect to query analyzer as that user and select/insert/update the tables within it. The user most def has rights to access the db...

I thought there may be probs with my odbc drivers but the same thing happens on other machines as well.... ????

 

[fuzzyocelot]

Out of curiosity, what ODBC driver version are you using?

 

[divinyl]

well, im using MDAC 2.8 on a win xp machine. Just gone into the About section in the ODBC Data Source Administrator and the version seems to be 3.525.1117.0

I've tried this on a windows 2003 server as well and had the same problem.

Thx