Created a new database on SQL Server, can't be seen by web apps because permissions aren't set.

Question

So I created a new database on my SQL Server box and then added an ODBC entry so my ASP code knows what it is. Now I am getting this error:

Cannot open database "DB_NAME" requested by the login. The login failed.

I checked out the permissions by right clicking the db in Management Studio and checked permissions and low and behold it is empty.

I am just trying to duplicate the permissions of one of any of the other twenty or so databases sitting on the box. Is there a quick way to do this? Either way I just need to open up the lines of communication between my ASP code and my SQL Server db

Here is my connect code in ASP:

Set sqlConnection = Server.CreateObject("ADODB.CONNECTION")
sqlConnection.Open "DB_NAME"

Answer 1

Try adding the user id and password into your connection string.

Answer 2

Is there some reason to avoid using a proper connection string:-

sqlConnection.Open "Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=DB_NAME;User Id=myUsername;Password=myPassword;"

Answer 3

Hi,

On top of what John said, when you create a new database, you have to map the login (either SQL login or Windows login from Application Pool) to this database. You can use management studio to do this - open login properties and map it to the new database. Open Object Explorer, then click Security, Logins and rightclick login that is used by your application. Select Properties. Click User Mapping and add appropriate entry for your database in the grid you will see. The other option is to run statement to create user within the database:

Use DB_NAME
go
create user [web_user] from login [web_login]
go

Another question is to see what rights have to be assigned to the user in the database. You have to check users permissions in one of the existing databases. Again in Object Explorer, click your existing database, then Security, Users and rightclick Properties of the user that you want to check. Observe information displayed on the dialog box in the General tab and check if there are any Securables assigned to the user. You have to copy these settings to the user in your new database.

HTH

Piotr