question

MichaelHoward-1713 avatar image
0 Votes"
MichaelHoward-1713 asked ·

"Model" Database Eqivilent for Azure SQL as a Service

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)

sql-server-generalazure-sql-database
· 1
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@MichaelHoward-1713 - Please let us know if you need further help with this.

0 Votes 0 ·

1 Answer

DanGuzman avatar image
0 Votes"
DanGuzman answered ·

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.

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.