Help with a SQL Server 2000 issue

Hi,

I signed up with a hosting service (brinkster.com) – they support SQL Server 2000.

I connected to the remote database using Enterprise Manager without any issues.

I also found an article by Scott Guthrie on how to change the providers to use SQL 2000 instead of SQL Express,(http://weblogs.asp.net/scottgu/archive/2005/08/25/423703.aspx),

but when I got to the part where I have to select a sever and a database (step 3 of the wizard), I got the following message

"Query database list failed"

"Failed to query a list of databse name from the SQL server.

Invalid object name 'sysdatabases'."

I ran the wizard on a local SQL Server and it worked fine!

Is there a workaround?

Please help… Thanks.

When using the wizard, make sure the account used to connect to the SQL2000 has select permission on the sysdatabases, and the default database of the account is master.|||

Thanks, I have been trying to figure out how to make sure the account has select permission on the sysdatabases, and that the default database is master.

I tried connecting to the master database and query the users table, I got an error message saying "Login failed for user 'user'"

It would be great if you can help out some more - point to more documentations/articles.

Again, Thaks.

|||

samirayes:

I tried connecting to the master database and query the users table, I got an error message saying "Login failed for user 'user'"

OK it seems to be a permission issue. What are you using to connect? You can easily check the permissions in Enterprise Manager: connect to the SQL instance-> go to Security->Logins->check the property of the 'user' login (if it is not there, create it)

|||

Brilliant - lori_jay you are fantastic. Thank you.

On the user's properties, I changed the default database to master (it was set to the database set up by the hosting service for the account)

I ran the wizard and voila - it worked like it should.

Do I have to change the default database to what it was, or should I keep it set to Master?

Again, thank you very much.

|||

samirayes:

Do I have to change the default database to what it was, or should I keep it set to Master?

In most scenarios, we can just set the default database to any database we need for the login--this option is just a part of security (login) setting. So there is no need to keep default database as Master, in this case we just set it to master to get the aspnet_regsql utility work Actually the aspnet_regsql could be better: if it retrieve database information from 'master..sysdatabases' not just 'sysdatabases', we do not have to change the default database for the login used in the wizard.