0

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

Cheers

2

A separate schema will provide a security boundary and simply securty administration. Below is an example script that creates a schema for the team's objects with a schema-specific owner and grants permissions to create objects.

CREATE USER BOBO WITHOUT LOGIN;
GO
CREATE SCHEMA BOBO AUTHORIZATION BOBO;
GO
GRANT CONTROL ON SCHEMA::BOBO TO [<BOBO team AD group>];
GRANT CREATE TABLE TO [<BOBO team AD group>];
GRANT CREATE VIEW TO [<BOBO team AD group>];
GO

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.