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

Posted: February 29, 2012 in BizTalk
Tags: , ,

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 database or that tries to execute stored procedure does not have sufficient permissions.

SOLUTION

You need to grant permission to the user to access database and/or to execute 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 user name 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 Permissions tab
    • Select the user in Users or roles panel
    • And on Permissions for user panel check grant for Exetute permission

Granting-execute-permissions-to-all-stored-procedures-in-database

Tags: BizTalk | SQL | Errors and Warnings, Causes and Solutions

About these ads
Comments
  1. Ryan says:

    Really helpful, Thank you for sharing this information.

  2. Morris says:

    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

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s