SQL Adapter -Stored Procedure Permissions- issue, -SQL 2005-

Our SQL security requires that Users have permission to execute stored
procedures. We do this by adding BTSService user within the SQL 2005
'Security Users' of the database, then we add that user to a 'Roles
Database Roles' such as 'db_approle' that is assigned to the
individual stored procedure and is then granted 'Execution'
privileges. You can see where the permissions are applied if you
right click any stored procedure and select 'Properties'. This works
quite nicely when schemas are created within BizTalk 2006 using the
SQL Adapter schema generation wizard and when the schema is created
from a stored procedure.

Here is the question. Within this framework, and when an UpdateGram
schema is created/used, how can privileges be granted to allow the
UpdateGram to access the DB? Here is the log entry showing the denial
when an UpdateGram was executed.

Error Description: HRESULT="0x80040e09" Description="UPDATE permission
denied on object 'CIPAssets', database 'CIP', schema 'dbo'."
HRESULT="0x80040e09" Description="SELECT permission denied on object
'CIPAssets', database 'CIP', schema 'dbo'."

Thank you,
Tom

.

Relevant Pages

  • Re: Business objects, subset of collection
    ... SQL only works when the statements are ... all items (invoices) are subscribing for events. ... all items and all items has to execute the criteria evaluation, ... The features of a OO database is basically the same as of a network ...
    (comp.object)
  • Split messages
    ... This is based off the northwind database. ... Create document schema: ... Select the SQL entry, make sure that we are pointing to the SQL ... For the item select a receive pipeline and name it EmpSplitPipe.btp. ...
    (microsoft.public.biztalk.general)
  • Re: Multiple Database Security - How to handle
    ... There is no 'execute as' in SQL Server but you can simplify security ... Assuming the DM database contains tables that are accessed only by ...
    (microsoft.public.sqlserver.security)
  • Re: Decouple SQL queries from class in OOP design
    ... If the data is owned by the application, embedding SQL might be ... - It couples the application to the database schema. ... - The "validate employee ID" functionality is likely to be used ...
    (comp.object)
  • Re: Need help on how to organize users and objects
    ... create and execute procedures and such ... tables they need and I grant them select permissions to them. ... so I think that the best way will be to create Schema to Role and then you ... and then create an appropriate permissons for those roles in database ...
    (microsoft.public.sqlserver.security)