Sql Database Access

Description

Creating user with permissions to sql db

Give access to pod identity - grant only select and exec

     if (select count(*) from sys.database_principals where type = 'R' and name = 'role-minority-dev-sql-galileo') = 1
        alter role [role-minority-dev-sql-galileo] drop member [minority-dev-sql-galileo];

    drop user if exists [minority-dev-sql-galileo];
    drop role if exists [role-minority-dev-sql-galileo];

    create role [role-minority-dev-sql-galileo];
    grant select, execute to [role-minority-dev-sql-galileo];
    create user [minority-dev-sql-galileo] from external provider;
    alter role [role-minority-dev-sql-galileo] add member [minority-dev-sql-galileo];

---

Create reader role on Dev

    if (select count(*) from sys.database_principals where type = 'R' and name = 'role-minority-dev-sql-readers') = 1
        alter role [role-minority-dev-sql-readers] drop member [minority-dev-sql-readers];

    drop user if exists [minority-dev-sql-readers];
    drop role if exists [role-minority-dev-sql-readers];

    create role [role-minority-dev-sql-readers];
    grant select to [role-minority-dev-sql-readers];
    create user [minority-dev-sql-readers] from external provider;
    alter role [role-minority-dev-sql-readers] add member [minority-dev-sql-readers];

Create Contributor role on Dev

     if (select count(*) from sys.database_principals where type = 'R' and name = 'role-minority-dev-sql-contributor') = 1
        alter role [role-minority-dev-sql-contributor] drop member [minority-dev-sql-contributors];

    drop user if exists [minority-dev-sql-contributors];
    drop role if exists [role-minority-dev-sql-contributor];

    create role [role-minority-dev-sql-contributor];
    grant select, insert, update, delete, execute to [role-minority-dev-sql-contributor];
    create user [minority-dev-sql-contributors] from external provider;
    alter role [role-minority-dev-sql-contributor] add member [minority-dev-sql-contributors]; 

Create reader role on Stage

     if (select count(*) from sys.database_principals where type = 'R' and name = 'role-minority-stage-sql-readers') = 1
        alter role [role-minority-stage-sql-readers] drop member [minority-stage-sql-readers];

    drop user if exists [minority-stage-sql-readers];
    drop role if exists [role-minority-stage-sql-readers];

    create role [role-minority-stage-sql-readers];
    grant select to [role-minority-stage-sql-readers];
    create user [minority-stage-sql-readers] from external provider;
    alter role [role-minority-stage-sql-readers] add member [minority-stage-sql-readers];

Create Contributor role on Stage

    if (select count(*) from sys.database_principals where type = 'R' and name = 'role-minority-stage-sql-contributor') = 1
        alter role [role-minority-stage-sql-contributor] drop member [minority-stage-sql-contributors];

    drop user if exists [minority-stage-sql-contributors];
    drop role if exists [role-minority-stage-sql-contributor];

    create role [role-minority-stage-sql-contributor];
    grant select, insert, update, delete, execute to [role-minority-stage-sql-contributor];
    create user [minority-stage-sql-contributors] from external provider;
    alter role [role-minority-stage-sql-contributor] add member [minority-stage-sql-contributors];

Create Reader role on prod

   if (select count(*) from sys.database_principals where type = 'R' and name = 'role-minority-prod-sql-readers') = 1
        alter role [role-minority-prod-sql-readers] drop member [minority-prod-sql-readers];

    drop user if exists [minority-prod-sql-readers];
    drop role if exists [role-minority-prod-sql-readers];

    create role [role-minority-prod-sql-readers];
    grant select to [role-minority-prod-sql-readers];
    create user [minority-prod-sql-readers] from external provider;
    alter role [role-minority-prod-sql-readers] add member [minority-prod-sql-readers];

Create Contributor role on prod

if (select count(*) from sys.database_principals where type = 'R' and name = 'role-minority-prod-sql-contributor') = 1
        alter role [role-minority-prod-sql-contributor] drop member [minority-prod-sql-contributors];

    drop user if exists [minority-prod-sql-contributors];
    drop role if exists [role-minority-prod-sql-contributor];

    create role [role-minority-prod-sql-contributor];
    grant select, insert, update, delete, execute to [role-minority-prod-sql-contributor];
    create user [minority-prod-sql-contributors] from external provider;
    alter role [role-minority-prod-sql-contributor] add member [minority-prod-sql-contributors];