Team blog of MCS @ Middle East and Africa

This blog is created by Microsoft MEA HQ near shoring team, and it aims to share knowledge with the IT community.With its infrastructure and development sides,It brings to you the proven best practices and real world experiences from Subject Matter Experts
Follow Us On Twitter! Subscribe To Our Blog! Contact Us

Resolved: BAM deploy view error SELECT permission was denied

Resolved: BAM deploy view error SELECT permission was denied

  • Comments 1
  • Likes

I faced a strange issue were I was trying to deploy a new BAM activity and view using the bm.exe tool. Whenever I executed the deploy-all command it simply reports that it deployed the activity and fails while deploying the view with an error like this:

OLE DB error: OLE DB or ODBC error: The SELECT permission was denied on the object ‘bam_<Some view name>_RTATable’, database ‘BAMPrimaryImport’, schema ‘dbo’.; 42000.

I then go to the BAMPrimaryImport database and find that nothing is deployed!

Actually this is not entirely true as when I opened a SQL profiler trace I found that it is really deploying the activity and then re-trackting everything once the view deployment fails. So what I found is that the utility would execute a SELECT statement on the given table in the BAMPrimaryImport database using the identity of the SQL server service account!!! And as this account really has no access to the database it fails! I do not know why it tries to use the service account but I was able to resolve this by giving the SQL service account db_datareader permissions to this database and it turned out in this case also I need to give this account the BTS_Admin_Users permissions on the BAMStarSchema database.

Comments
Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment