SQL SERVER – FIX : ERROR : Cannot open database requested by the login. The login failed. Login failed for user

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

SQL SERVER – Fix: Error: 15138 – The database principal owns a schema in the database, and cannot be dropped

Last day I had excellent fun asking puzzle on SQL Server Login SQL SERVER – Merry Christmas and Happy Holidays – Database Properties – Number of Users. One of the user sent me email asking urgent question about how to resolve following error. Reader was trying to remove the login from database but every single time he was getting error and was not able to remove the user.

The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)

As per him it was very urgent and he was not able to solve the same. I totally understand his situation and here is the quick workaround to the issue. The reason for error is quite clear from the error message as there were schema associated with the user and that needs to be transferred to another user.

Workaround / Resolution / Fix:

Let us assume that user was trying to delete user which is named as ‘pinaladmin’ and it exists in the database ‘AdventureWorks’.

Now run following script with the context of the database where user belongs.

USE AdventureWorks;SELECT s.nameFROM sys.schemas sWHERE s.principal_id = USER_ID('pinaladmin');

In my query I get following two schema as a result.

Now let us run following query where I will take my schema and and alter authorization on schema. In our case we have two schema so we will execute it two times.

ALTER AUTHORIZATION ON SCHEMA::db_denydatareader TO dbo;ALTER AUTHORIZATION ON SCHEMA::db_denydatawriter TO dbo;

Now if you drop the database owner it will not throw any error.

Here is generic script for resolving the error:

SELECT s.nameFROM sys.schemas sWHERE s.principal_id = USER_ID('YourUserID');

Now replace the result name in following script:

ALTER AUTHORIZATION ON SCHEMA::YourSchemaName TO dbo;