Sql-server – Azure SQL – Setting up permissions for users

azure-sql-databasesql serverssms

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

Best Answer

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
Related Question