I'm busy playing around within Azure SQL and have setup replication from my on-prem DB to the Azure DB for some reporting. I would like to ensure as part of this we setup the security correctly. In this instance I have a "Team" who do the BI/BO reporting (lets call them BIBOTeam). I would like them to have Read only access (db_datareader) on the Synced data, but they will need to create some Views (and potentially tables) on that database for their BI system.
They don't want to create the views in the "source" database as it's a vendore provided system.
I'm assuming schemas/roles is the way to go? I have an AD group called [GRP BIBOTeam] which is currently setup correctly and they can log into the DB and only read data in the Synced database.
Would love some guidance on what path is best to manage this access