Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
 Posted - 2009-05-04 : 18:12:30
|
I have SQL 2000 db (updgrading to 2005 in the near future).Friday, everything worked fine...today, not so much.Our users connect to our data (just a warehouse, no application back ends and such) for ad-hoc queries and reporting through Access, SAS, excel etc...via ODBC. Of the 15 databases this user id can see, only 3 remain visible. Nothing has changed on the server..it has been BAU. I restored saturday nights backup of one of the databases, and the same issue persists.sa logins can still see everthing, although when even entering these when prompted from MS Access, there are connection problems. This is highly odd, and even creating a brand new user with one of the databases as default fails with the same error. We have even deleted the user from the server entirely, recreated from scratch and same error.All of the logins are in sync, there doesn't appear to be any issue with the SQL side of things...Also, oddly, preexisting queries via MS Excel etc remain working with the same login id that fails in ODBC. Also, the id can successfully connect and read data/expand objects as permitted via Enterprise manager.I have exhausted all of my ability to try and diagnose this, as did our database services team..so I come here to the experts for any possible guidance.I am not sure, but the database proc that populates the ODBC dropdown would be my first guess to check..but I am not even sure where to look. Everything database side returns proper results for the login.SQL 2000 / SP3 ( I know, I know...)Ideas?
Poor planning on your part does not constitute an emergency on my part. |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2009-05-04 : 22:16:23
|
Ugh...there is SQLDumpexception handler errors the dump indicates the error is on Select name from master..sysdatabases where has_dbaccess(name) = 1...for some reason, a specific login which has db_datareader role in all user databases is only seeing 3 of the 15. Still have no idea why...everything checks out near as I can tell so far. All users and all dbs are fine. no dbcc checkdb errors or user issues are apparent.
Poor planning on your part does not constitute an emergency on my part. |
 |
|
|
|
|
|
|
|
"Database not valid" ODBC SQL 2000?