July 1, 2008 at 11:15 pm
Hi all. I am running into what appears to be a permission or authentication issue with SQL server 2005 after migrating DBs from SQL 2000.
We have a simple stored procedure call being made via Microsoft query in an Excel document and its receiving he following message that Excel (Office 2003) is taking as a critical error:
[Microsoft][ODBC SQL Server Driver][SQL Server]changed database context to 'DBname'
Error comes right up when its attempting to connect to the DB.
I run it locally on my machine and it works, difference is I am in sysadmin. I added sysadmin to the user and works fine.
I took sysadmin away and threw them an Access DB with a pass thru query. Using their standard permissions it worked fine.
Seems to preclude a permission issue as it works fine thru Access and also no issue with the ODBC as it again worked thru Access. As it ran fine thru Excel / MS query iagainst the 2000 server with identical permissoins it leads me to conclusions there is some issue with 2005 authentication and how it addresses ODBC connection thru MS query or Excel. I cant find an knowledge base articles that seem to fit this outside general reference to information message errors that apps like excel should ignore.
Any insight would be greatly appreciated.
Thanks much.
July 2, 2008 at 8:21 am
Have you checked that the regular login you're using exists at both the server and database level in the 2k5 server?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 2, 2008 at 12:55 pm
Roger that. I had to verify they had privelege to exec the SP which is at the DB level. Not seeing any differences in permissions between the 2 versions from the user or their assigned role perspective.
July 3, 2008 at 11:11 am
Only thing I can think of is that I have had to re-create some database level user-accounts after migrating from 2000 to 2005. If you've handled that, I'm stumped on this one.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 3, 2008 at 12:19 pm
did you change the Database compatibility level to 90 after the migration.
and also did you check for orphaned logins in the database.
Use databasename
sp_change_users_login 'REPORT'
to update the login info:
sp_change_users_login 'UPDATE_ONE',loginname','loginname'
did you move the logins associated with the database...?
Maninder
www.dbanation.com
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply