Setting up permissions

[poger67]

I've just setup my first SQL 2000 Server (running on W2K Server along with Cold Fusion 5 Server). I used the import data to convert one of my current access databases to SQL, and that seemed to go OK.

I then setup a DSN (Control Panel > Administration > ODBC Connections) called mmw2. I then went to the CF Administrator and setup the corresponding DSN there - database verified fine there and in the SQL enterprise manager.

I setup a login for the database with username 'mmw' and a password.

When I open the main site page though, I'm getting an ODBC error as follows:

Error Diagnostic Information
ODBC Error Code = 42000 (Syntax error or access violation)

[Microsoft][ODBC SQL Server Driver][SQL Server]SELECT permission denied on object 'CGLOBAL', database 'master', owner 'dbo'.

I see that it's trying to hit the 'master' database (there are no actual queries on the page that I'm trying to load). Do I need to give my db user 'mmw' some permissions to the master db also?

This is going to be a db driven web site (it's in access now and running fine, but I want to learn SQL Server setup). What should I do to get this site running? What else should I do (security wise etc.) to make sure I'm doing the proper steps for what will be a web server. I've run Baseline Security Analyzer and IIS Lockdown so far.

Thanks!

 

[SQLBill]

Nope, what probably happened is that when you assigned the login to SQL Server you set the default database as MASTER. If the user isn't supposed to have access to MASTER, change the default database. Or you can change the ODBC connection. Go to the ODBC connection wizard. Click on your ODBC connection and select CONFIGURE. Go to the screen with the login prompt. Go to the NEXT screen. At the top should be an option to set the database. Make sure it's the proper database.

-SQLBill