Cannot open database Login failed error using secondary logon

Hello all,

I think I am having problems with associating my main SQL login with my two secondary logins. I created two additional SQL server 2005 logins using the Login Manager ok, but when I try to use them in my connection string of my ASP.Net 3.5 MVC application I get a "Cannot open database Login failed for user..." error message.

I'm convinced I have the correct login and PW in the connection string because of these two things

1) I ran sqlcmd -S sql2k508.discountasp.net -U MYSECONDARYLOGIN -P XXXXXXXXXXXXX
which returns key 1>
See here.

2) I can logon remotely with my secondary login credentials using SSMS but I can't do anything else. I can see the databases in the object explorer tree but as soon as I try to expand it get a database is not accessible error.

I'm pretty sure I have done something wrong with not properly associating my primary login account with my secondary logins but I don't know what. I got this to work before in the passed a couple of months ago without having to resort to using my primary login credentials in the connection string. The problem is that I deleted that back up and can't go back to compare with where I'm at now.

Does anyone have any ideas, or maybe a check list

 

I did that already (except it was for 2005).

I have created another problem though. I was playing around with the primary account by adjusting what DB members it can be a part of to see how this would effect my application which is currently using the primary credentials. The last thing I did was remove the DB ownership role (which did cause my ASP app to finally crash) but know I can't get that back.

How do I fix that, or did I paint my self into a corner?


http://support.discountasp.net/KB/a196/how-do-i-assign-additional-sql-logins-to-my-sql-2005.aspx

Step 7 says this.

In the Database User Properties
* Type the username you want to assign to this login in the "User name" text box
* Type the login you want to assign to the database in the "Login name" text box
* In the Database role membership, select the role you want to grant this login.

My question is what is the distinction between a user and a login? I used the secondary login name that I created from the Login Manager and used it for both the user name and the login name. Is this correct?


Here is the Error

Add member failed for DatabaseRole 'db_owner'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?Prod...xt&EvtID=Add+member+DatabaseRole&LinkId=20476

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

User does not have permission to perform this action. (Microsoft SQL Server, Error: 15247)

 

db_owner ERROR FIXED

I got this fixed via the problem ticket, but I still would like to figure out how to do use a secondary logon for my connection string

 

I am running into this same problem. I created the secondary user, then in my database I added this user under the security folder, then added him to all roles. I get the "Cannot open database "SQL2008_xxxxxx_xxx" requested by the login. The login failed. Login failed for user 'xxxxxx'. What else needs to be done??? Did you ever figure this out?

 

Will try again, thanks.