Error Fixed Cannot open database "dbname" requested by the login. The login failed. Login failed for user 'Machine\ASPNET' In ASP.NET and SQL express

One of the readers was using windows authentication in SQL connection string. When he tries to access database from connection string from Windows Application, it was working but when he tries to access same database with same connection string, it was throwing above error.

Main reason behind this error is permission issue. Now, question pops up in your mind, if that is a connection issue than why it was allowing same connection string in Windows application? Simple logical reason is that, Windows application uses local system account to connect with database when connection string built for Windows Authentication and ASP.NET uses ASPNET user of the web-server to connect with database. If your ASPNET user don’t have permission to access database, you will greeted with error

“Cannot open database ‘databasename’ requested by the login. The login failed for user ‘Machine-name/ASPNET’”.

So, now you know this is a permission issue, as soon as you will grant access to your db to ASPNET user, you will be ok but what, if you don’t have SQL Server client tool? It is quite possible if you are having SQL Express edition.

First let us see the command which will helpful to give permission to ASPNET user.

EXEC sp_grantlogin 'Machine-name\ASPNET'

USE ADVENTUREWORKS--(YOUR DATABASE NAME)

EXEC sp_grantdbaccess 'Machine-name\ASPNET'

EXEC sp_addrolemember 'db_owner', 'Machine-name\ASPNET'

Run above script and you are done but what if you don’t have SQL Server client tool? You have to use OSQL utility from DOS prompt to run above command.

After getting DOS screen, follow below given command.

Note: “Ritesh” is my webserver name where ASPNET user resides. "." in OSQL command represent my databse server you can give your SQL server's IP or Name there, like: 'Localhost\SQLExpress'

C:\Documents and Settings\Ritesh>cd\

C:\>osql -S . -E

1> sp_grantlogin 'Ritesh\ASPNET'

2> GO

1> use adventureworks

2> GO

1> sp_grantdbaccess 'Ritesh\ASPNET'

2> go

1> SP_addrolemember 'db_owner','Ritesh\aspnet'

2> go

So, here with you will solve your error with above command along with OSQL utility which is good utility itself.

Enjoy !!!!

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com