June 15, 2003 at 11:03 pm
I need some help. When I create a system DSN in Windows, all the steps are not not working. I can enter the name of the "remote" SQL Server 2000 ... if remote is the correct term. I can enter the desired Userid / Password. The only way the DSN passes ODBC the test is if I use the Master database that comes with SQL Server. The DSN create gives me a drop down list of databases on the SQL Server box. It only lists those databases that come with SQL Server. IOW, mine are *not* listed 🙁
I looked at the properties of the Master database and mine. I changed the two parameters that I had different. No difference. So I changed them back.
My DSN works fine on the test server. The test server has IIS and SQL Server on the same box. In the new setup, IIS is on one box and SQL Server is on another box. All three servers are running Win2K.
What am I missing? Thanks in advance.
-brian
June 16, 2003 at 1:45 am
ODBC will only list those databases that your Userid has permissions for.
Check in Enterprise manager to see if your UserId has permissions to the missing databases.
June 16, 2003 at 8:40 pm
Yonah -
Thanks for the prodding. It helped me figure it out.
We backed up the databases on the test machine. Then moved the backup files to the new SQL Server box. Finally we restored the databases. A review of the Users for each database showed all the authorizations moved from the test box to the production box. A further review of Security in the tree revealed a difference. None of the permissions moved! Trying to add the authorizations fails because the authorizations already exist!
I fixed the authorizations one database at a time. First, write down the authorizations for the User. Second, delete that User from the Users under that database. Third, add the User back to the database. Fourth, set the authorizations to what was written down. A review of Security shows this is automatically populated.
If Microsoft bothered to know the history of databases, this would never have happened. Databases were created to resolve the issue of multiple copies of the same information (in different files). The basic idea was a single repository (copy) of the information. </soapbox>
-brian
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply