I suggest you include the script as part of your deployment process and avoid using model even for local SQL instances. Tooling, such as SSDT and source control, can facilitate consistent deployment of both schema and permissions.
"Model" Database Eqivilent for Azure SQL as a Service
Michael Howard
21
Reputation points
I am looking to mirror Automated Permissions, that have been set up on an SQL Server that runs on windows server locally, that will automatically assign permissions to users/groups when a database is created.
On the Local SQL this was done using the below SQL script to add the desired permissions to the model database.
USE model;
GO
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'Domain\Group')
BEGIN
CREATE USER [Domain\Group] FOR LOGIN [Domain\Group]
END
GO
ALTER ROLE db_datawriter ADD MEMBER [Domain\Group];
ALTER ROLE db_datareader ADD MEMBER [Domain\Group];
GO
However, as there is no "model" database in AzureSQL is there an alterative method for this native to azure , or should we look to automate via other tooling (Such as Terraform or incorporating into the DACPAC project we use to deploy)