“Cannot open database ‘…’ requested by the login. The login failed” or “The EXECUTE permission was denied on the object ‘…’, database ‘…’, schema ‘dbo’”

It’s very common for orchestrations to access databases or executing stored procedures. Whether this access is done by SQL Adapter, WCF-SQL adapter or through C # code if we are not careful to set the user permissions properly in the database, we can get one of these errors:

Cannot open database ‘…’ requested by the login. The login failed

or:

The EXECUTE permission was denied on the object ‘…’, database ‘…’, schema ‘dbo’

Cause

This problem occurs because the user account that you used to access the database or that tries to execute stored procedure does not have sufficient permissions.

Solution

You need to grant permission to the user to access the database and/or to execute the stored procedure.

Normally orchestrations are running under BizTalk Application Users group but you have to check the windows group configured on the host instance that runs the orchestrations which want to access the database.

How to grant reader/writer access to SQL database:

  • In SQL Server Management Studio, open Object Explorer and expand it to the database that you want to give access
  • Right-click the Security folder, point to New and then click User.
SQL Add User login
  • On the General page, enter username and login in the Login name box.
  • Set the following Database role membership:
    • db_datareader
    • db_datawriter
How grant reader writer access SQL database
  • Click OK.

Granting execute permissions to all stored procedures in a database:

  • In SQL Server Management Studio, open Object Explorer and expand it to the database that you want to give execute access
  • Right-click the database name and then click Properties.
  • In the Permissions tab
    • Select the user in Users or roles panel
    • And on Permissions for user panel check grant for Execute permission
Granting execute permissions to all stored procedures in database
#1 Azure Monitoring Platform
Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.

4 thoughts on ““Cannot open database ‘…’ requested by the login. The login failed” or “The EXECUTE permission was denied on the object ‘…’, database ‘…’, schema ‘dbo’””

  1. Hello There. I found your blog the use of msn. That is a very well written article. I’ll make sure to bookmark it and return to read more of your useful info. Thanks for the post. I will certainly comeback.

Leave a Reply

Your email address will not be published. Required fields are marked *

turbo360

Back to Top