SQL SERVER – FIX : ERROR : Cannot open database requested by the login. The login failed. Login failed for user ‘NT AUTHORITY\NETWORK SERVICE’.

August 20, 2009 by pinaldave

This error is quite common and I have received it few times while I was working on a recent consultation project.

Cannot open database requested by the login. The login failed.
Login failed for user ‘NT AUTHORITY\NETWORK SERVICE’.

This error occurs when you have configured your application with IIS, and IIS goes to SQL Server and tries to login with credentials that do not have proper permissions. This error can also occur when replication or mirroring is set up.

If you search online, there are many different solutions provided to solve this error, and many of these solutions work fine. However, I will be going over a solution that works always and is very simple.

Fix/Workaround/Solution:

Go to SQL Server >> Security >> Logins and right click on NT AUTHORITY\NETWORK SERVICE and select Properties

In newly opened screen of Login Properties, go to the “User Mapping” tab. Then, on the “User Mapping” tab, select the desired database – especially the database for which this error message is displayed. On the lower screen, check the role db_owner. Click OK.

In almost all such cases, this should fix your problem.

Reference : Pinal Dave (http://blog.sqlauthority.com)

Posted in Pinal Dave, SQL, SQL Authority, SQL Error Messages, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Tips and Tricks, T SQL, Technology | Tagged SQL Login | 12 Comments

12 Responses

on August 20, 2009 at 11:34 am | Reply Gangadhar Naidu

Simple and nice explanation.
on August 20, 2009 at 6:41 pm | Reply MERGEC

Is there any other way around this without having to give db_owner rights? Thanks.
on August 20, 2009 at 6:43 pm | Reply krunal

Excellent explanation.
thanks..
on August 27, 2009 at 5:23 pm | Reply Devesh

Hi,

Good to see your blog. It contains a lot of useful info. However, my problem doesn’t get resolved with this article.

I have a sharepoint server (2007) configured and while setting up the configurations, i started to get this error:

Login failed for user ‘NT AUTHORITY\LOCAL SERVICE’. [CLIENT: xxx.xxx.xxx.xxx]

Description: Cannot open database “SharedServices1_DB_8b3d821a-aea9-425d-bac9-87b4b4c1ac70″ requested by the login. The login failed.

my Shared Service Provider is configured to run under the above account with permissions as you mentioned above in your article.
on September 25, 2009 at 7:53 am | Reply lim

Hi,
The database is set as ’standby and read only’ mode because the database being set as log shipping (subscriber). I can’t change as per your recommendation. What should be done before that?
Thanks.
on September 30, 2009 at 5:57 am | Reply Donna

Thanks so much! that helped!
on October 7, 2009 at 3:11 pm | Reply hamidi

hi
when i enter the following command in the command prompt:

psconfig -cmd standaloneconfig

four steps pass without any problem. in the step 5 of 10, i get the following error:

Failed to register SharePoint services.

An exception of type System.Data.SqlClient.SqlException was thrown. Additional exception information: Cannot open database “SharedServices1_DB_af35e620-5472-42d9-af7a-b78a20caf33e” requested by the login. The login failed.
Login failed for user ‘TEST\Administrator’.

since the problem seems to be the same as what u mentioned here, i tried the solution. but, in the User Mapping section of the Login Properties of NT AUTHORITY\NETWORK SERVICE, i couldn’t find SharedServices1_DB_af35e620-5472-42d9-af7a-b78a20caf33e. there are totally 8 items in the list, four disabled:

master
model
msdb
tempdb

and four enabled:

SharedServicesContent_7e…c9
SharePoint_AdminContent_eb…91
SharePoint_Config_18…ac
WSS_Content

the disabled ones’ db_owner is unchecked and the enabled ones’ db_owner is checked. i mean the role list is disabled or enabled.

now, what can i do?
thx
on October 7, 2009 at 3:21 pm | Reply hamidi

besides, when opening the User Mapping page, Microsoft SQL Server Management Studio pops up an info dialog indicating:

One or more databases are inaccessible and will not be displayed in the list.
on October 17, 2009 at 3:34 am | Reply Namrata

Hi Pinal,

I have restored a database and I get this error while i try to connect my application to the database. It fails for my application user name and not for NTAuthority.
Can you suggest what could be the problem.

Thanks
Namrata
on October 30, 2009 at 3:18 pm | Reply HC Lim

Thanks! it solve my problem within second that i had debugging the error few days!
on November 8, 2009 at 4:23 pm | Reply Vijayakumar

Excellent thank you..
on November 20, 2009 at 10:15 pm | Reply Nonso

Thanks man.. you are the best..
The tip worked like charm..