Greetings all,
This should be an easy task, and Im sure it is, but as many times as I have
tried, I cant seem to get this to work properly.
We changed ISPs recently from a shared host to a co-located server, and our
former host was nice enough to send us a backup of our old SQL2000 database
(about 5MB).
I went into Enterprise Manager, created an empty database with the same name
and used the Restore Backup tool successfully. I took a look at the db and
saw all the Tables and data intact.
Next I wanted to re-create the DSN to match the old one so that I wouldnt
have to fiddle with any of the old connection strings in my asp pages.
Thats where the fun began. The old DSN was created by our host via an email
request and I never got a look at the actual creation process of that DSN.
I created one on our colo server with the same name, but it would only pass
the connection tests when I used the Windows User Authentication rather than
SQL Server Authentication. No big deal I figured, and just went ahead and
set it up using the same DSN name.
Next I tried the main.asp page to test the DSN and lo and behold I got the
ODBC connection errors. I tinkered with the connection strings a bit and
managed to get a wide variety of connection errors and fine-tuned to the
point that it said "Unable to login with user 'SERVER169/nacog'". At this
point, I went into Enterprise manager and added 'nacog' to the User list and
the connection string no longer produced errors. (By the way, my connection
string simply contains "DSN=YAVAPAICONNECT;")
My next step was to actually execute a SELECT statement which produced the
following error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e09'
[Microsoft][ODBC SQL Server Driver][SQL Server]SELECT permission denied on
object 'ADMINS', database 'YAVAPAICONNECT', owner 'dbo'.
/nacog/admin/main.asp, line 189
Why was I not surprised?
I went back to Enterpise Manager, saw the all the tables had 'dbo' as the
owner, and tried to give 'nacog' all the permissions at the table level, but
the error persists.
To preserve my sanity I stopped there, because I spun my wheels for hours
and days the last time this happened. and my eye has not stopped twitching
since ;)
I am quite sure that this has something to do with one or all of the
following:
1) The way SQL2K was installed (it was installed by someone else)
2) My creation of the db with the windows login, rather than SQL auth, prior
to import
3) My creation and handling of the DSN
My background is mainly in ASP programming, and I understand bits and pieces
of this puzzle, but for the life of me I cannot piece this thing together.
Can anyone help point me in the right direction or suggest a good tutorial?
I would be very thankful to anyone who could help put me on the right track.
Best Regards,
Ben M.