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
The EXECUTE permission was denied on the object ‘…’, database ‘…’, schema ‘dbo’
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.
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.
- On the General page, enter username and login in the Login name box.
- Set the following Database role membership:
- 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