Cannot open database “test” requested by the login

Question

I have created a web service which is saving some data into to db. But I am getting this error:

Cannot open database "test" requested by the login. The login failed. Login failed for user 'xyz\ASPNET'.

My connection string is

Data Source=.\SQLExpress;Initial Catalog=IFItest;Integrated Security=True

Answer 1

I found that I also had to set the UserMapping option when creating a new login and this solved the problem for me. Hope that helps anyone that also found themselves stuck here!

Edit: Setting the login as db owner solved the next problem, too

Answer 2

Some times this trouble may appear if you open this db in another sql server (as example, you launch sql managment studio(SMS) and add this db), and forget stop this server. As result - you app try to connect with user already connected in this db under another server. To fix that, try stop this server by Config. dispatcher sql server.

My apologies about bad english. Kind regards, Ignat.

Answer 3

I had this problem and what solved it for me was to:

• Go to the Application pools in the IIS
• Right click on my project application pool
• In Process Model section open Identity
• Choose Custom account option
• Enter your pc user name and password.

Answer 4

I tried to update the user, and it worked. See the command below.

USE ComparisonData// databaseName
EXEC  sp_change_users_login @Action='update_one', @UserNamePattern='ftool',@LoginName='ftool';

Just replace user('ftool') accordingly.

Answer 5

• Either: "xyz\ASPNET" is not a login (in sys.server_principals)
• Or: "xyz\ASPNET" is set up but not mapped to a user in the database test (sys.database_principals)

I'd go for the 2nd option: the error message implies the default database is either not there or no rights in it, rather than not set up as a login.

To test if it's set up as a login

SELECT SUSER_ID('xyz\ASPNET') -- (**not** SUSER_SID)

If NULL

CREATE LOGIN [xyz\ASPNET] FROM WINDOWS

If not NULL

USE test
GO
SELECT USER_ID('xyz\ASPNET')

If NULL

USE test
GO
CREATE USER [xyz\ASPNET] FROM LOGIN [xyz\ASPNET]

Answer 6

I used Windows authentication to connect to local database .mdf file and my local server was sql server 2014. My problem solved using this connection string:

string sqlString = " Data Source = (LocalDB)\\MSSQLLocalDB;" + "AttachDbFilename = F:\\.........\\myDatabase.mdf; Integrated Security = True; Connect Timeout = 30";