Create a User for Azure SQL Database from Azure DevOps Pipeline¶

To create a SQL User for use with Azure SQL Database:

Get the Azure AD id of the principal
Create database contained User
Grant a role to the database user

You might also need to be able to:

Remove a role from database user
Remove a database user

This gives you a full set of tools to manage Azure SQL Database users. You can use them to authenticate individual Azure AD users, users as members of Azure AD group, Azure AD Service Principal or Managed Identity.

Get Azure AD ID for Service Principal or Managed Identity¶

Following Azure PowerShell script will get the AD principal ID and publish it as pipeline variable.

/db-scripts/Get-ServicePrincipalAdIdentifier.ps1¶

param(
   [string]$AdPrincipalName,
   [ValidateSet('user','group','service-principal')]
   [string]$AdPrincipalType = 'service-principal',
   [string]$SidPipelineVariableName='AdPrincipalSid',
   [string]$IdPipelineVariableName=''
)

$adPrincipalId = if ($AdPrincipalType -eq 'service-principal') {
   (Get-AzADServicePrincipal -DisplayName $AdPrincipalName).AppId
} elseif ($AdPrincipalType -eq 'user') {
   (Get-AzADUser -DisplayName $AdPrincipalName).Id
} else {
   (Get-AzADGroup -DisplayName $AdPrincipalName).Id
}
$adPrincipalSid = "0x" + [System.String]::Join("", ((New-Object -TypeName System.Guid -ArgumentList $adPrincipalId).ToByteArray() | ForEach-Object { $_.ToString("X2") }))
Write-Host "For $AdPrincipalType '$AdPrincipalName' following ID was found: '$adPrincipalId'"
Write-Host "SID: '$adPrincipalSid'"


if ($SidPipelineVariableName -ne '') {
   Write-Host "Publish principal id to pipeline variable '$SidPipelineVariableName'"
   Write-Host "##vso[task.setvariable variable=$SidPipelineVariableName;]$adPrincipalSid"
}

if ($IdPipelineVariableName -ne '') {
   Write-Host "Publish principal id to pipeline variable '$IdPipelineVariableName'"
   Write-Host "##vso[task.setvariable variable=$IdPipelineVariableName;]$adPrincipalId"
}

Create Database Contained User for AD User¶

Following SQL script creates a database contained user and grants a db_datareader role to the user.

New-SqlDatabaseUserForPrincipal.sql¶

/*
.SYNOPSIS
   Create a SQL User for Azure AD Principal.

.DESCRIPTION
   Create a SQL User for Azure AD Principal.
*/

DECLARE @SqlCmd VARCHAR(4096);
DECLARE @SQLUserName VARCHAR(4096) = '$(SQLUserName)';
DECLARE @SQLUserSID VARCHAR(85) = '$(SQLUserSID)';
DECLARE @DefaultSchema VARCHAR(85) = '$(DefaultSchema)';

IF NOT EXISTS (SELECT TOP 1 *
               FROM sys.database_principals
               WHERE name = @SQLUserName)
   BEGIN
      SET @SqlCmd = 'CREATE USER [' + @SQLUserName + '] WITH SID = '+@SQLUserSID+', TYPE = X , DEFAULT_SCHEMA=[@DefaultSchema]'
      PRINT @SqlCmd
      EXEC (@SqlCmd)
   END
ELSE
   BEGIN
      RAISERROR ('SQL user ''%s'' already exists in ''sys.database_principals''.', 16, 1, @SQLUserName);
   END
BEGIN
   SET @SqlCmd = 'EXECUTE sp_addrolemember db_datareader, ''' + @SQLUserName + '''';
   PRINT @SqlCmd
   EXEC (@SqlCmd)
END

The SQL script could be executed using the SqlAzureDacpacDeployment task:

- checkout: self
   path: ./s
- task: SqlAzureDacpacDeployment@1
   displayName: 'Create SQL User'
   inputs:
      azureSubscription: '$(ServiceConnectionName)'
      AuthenticationType: 'servicePrincipal'
      ServerName: '${{ parameters.dbServerHost }}'
      DatabaseName: '${{ parameters.dbName }}'
      deployType: 'sqlTask'
      sqlFile: '$(Build.SourcesDirectory)/db-scripts/New-SqlDatabaseUserForPrincipal.sql'
      sqlAdditionalArguments: -Variable "SQLUserName=$(AdPrincipalName)", "SQLUserSID=$(AdPrincipalSid)", "DefaultSchema=$(SQLUserSchema)"

Define a Role for an Existing SQL User¶

Following SQL script could be used to define a role for an existing SQL user. You might notice that I have decided not to use the fixed SQL database roles, but use customized set of definitions.

/db-scripts/Add-SqlDatabaseUserRole.sql¶

/*
.SYNOPSIS
   Defines a role for an existing SQL database user.

.DESCRIPTION
   Defines a role for an existing SQL database user.
*/

DECLARE @SqlCmd VARCHAR(4096);
-- RoleName should be one of (db_backupoperator, db_writer, db_reader).
DECLARE @RoleName VARCHAR(4096) = '$(RoleName)';
DECLARE @SQLUserName VARCHAR(4096) = '$(SQLUserName)';
DECLARE @SQLUserSchema VARCHAR(128) = '$(SQLUserSchema)';

IF EXISTS (SELECT TOP 1 *
         FROM sys.database_principals
         WHERE name = @SQLUserName)
   BEGIN
      SET @SqlCmd = 'EXECUTE sp_addrolemember ''' + @RoleName + ''', ''' + @SQLUserName + '''';
      PRINT @SqlCmd
      EXEC (@SqlCmd)

      IF @RoleName = 'db_backupoperator'
            BEGIN
               SET @SqlCmd = 'GRANT VIEW DATABASE STATE TO ['+ @SQLUserName +']';
               PRINT @SqlCmd
               EXEC (@SqlCmd)
            END

      IF @RoleName = 'db_datawriter'
            BEGIN
               SET @SqlCmd = 'GRANT CREATE Table TO ['+ @SQLUserName +']';
               PRINT @SqlCmd
               EXEC (@SqlCmd)
               SET @SqlCmd = 'GRANT INSERT,UPDATE,DELETE,ALTER,EXECUTE,REFERENCES  ON SCHEMA::' + @SQLUserSchema + ' TO ['+ @SQLUserName +']';
               PRINT @SqlCmd
               EXEC (@SqlCmd)
            END

      IF @RoleName = 'db_datareader'
            BEGIN
               SET @SqlCmd = 'GRANT SELECT ON SCHEMA::' + @SQLUserSchema + ' TO ['+ @SQLUserName +']';
               PRINT @SqlCmd
               EXEC (@SqlCmd)
            END
   END
ELSE
   BEGIN
      RAISERROR ('SQL user ''%s'' does not exist in database ''sys.database_principals''.', 16, 1, @SQLUserName);
   END

The SQL script could be executed using the SqlAzureDacpacDeployment task:

- checkout: self
   path: ./s
- task: SqlAzureDacpacDeployment@1
   displayName: 'Create SQL User'
   inputs:
      azureSubscription: '$(ServiceConnectionName)'
      AuthenticationType: 'servicePrincipal'
      ServerName: '${{ parameters.dbServerHost }}'
      DatabaseName: '${{ parameters.dbName }}'
      deployType: 'sqlTask'
      sqlFile: '$(Build.SourcesDirectory)/db-scripts/Add-SqlDatabaseUserRole.sql'
      sqlAdditionalArguments: -Variable "SQLUserName=$(AdPrincipalName)", "SQLUserSID=$(AdPrincipalSid)", "SQLUserSchema=$(SQLUserSchema)"

Remove a Role From an Existing SQL User¶

Following script reverts the grants from the script to add role.

/*
.SYNOPSIS
   Removes a SQL Role from existing SQL Database user.

.DESCRIPTION
   Removes a SQL Role from existing SQL Database user.
*/

DECLARE @SqlCmd VARCHAR(4096);
-- RoleName should be one of (db_backupoperator, db_writer, db_reader).
DECLARE @RoleName VARCHAR(4096) = '$(RoleName)';
DECLARE @SQLUserName VARCHAR(4096) = '$(SQLUserName)';
DECLARE @SQLUserSchema VARCHAR(128) = '$(SQLUserSchema)';

IF EXISTS (SELECT *
         FROM sys.database_principals
         WHERE name = @SQLUserName)
   BEGIN
      SET @SqlCmd = 'EXECUTE sp_droprolemember ''' + @RoleName + ''', ''' + @SQLUserName + '''';
      PRINT @SqlCmd
      EXEC (@SqlCmd)

      IF @RoleName = 'db_backupoperator'
            BEGIN
               SET @SqlCmd = 'REVOKE VIEW DATABASE STATE FROM ['+ @SQLUserName +']';
               PRINT @SqlCmd
               EXEC (@SqlCmd)
            END
      IF @RoleName = 'db_datawriter'
            BEGIN
               SET @SqlCmd = 'REVOKE CREATE Table TO ['+ @SQLUserName +']';
               PRINT @SqlCmd
               EXEC (@SqlCmd)
               SET @SqlCmd = 'REVOKE INSERT,UPDATE,DELETE,ALTER,EXECUTE,REFERENCES  ON SCHEMA::' + @SQLUserSchema + ' TO ['+ @SQLUserName +']';
               PRINT @SqlCmd
               EXEC (@SqlCmd)
            END

      IF @RoleName = 'db_datareader'
            BEGIN
               SET @SqlCmd = 'REVOKE SELECT ON SCHEMA::' + @SQLUserSchema + ' TO ['+ @SQLUserName +']';
               PRINT @SqlCmd
               EXEC (@SqlCmd)
            END
   END
ELSE
   BEGIN
      RAISERROR ('SQL user ''%s'' does not exist in database ''sys.database_principals''.', 16, 1, @SQLUserName);
   END

Remove Existing SQL Database User¶

And the final piece to remove a SQL Database User:

/*
.SYNOPSIS
   Removes existing SQL Database User.

.DESCRIPTION
   Removes existing SQL Database User.
*/

DECLARE @SqlCmd VARCHAR(4096);
DECLARE @SQLUserName VARCHAR(4096) = '$(SQLUserName)';

IF EXISTS (SELECT TOP 1 *
         FROM sys.database_principals
         WHERE name = @SQLUserName)
   BEGIN
      SET @SqlCmd = 'DROP USER [' + @SQLUserName + ']'
      PRINT @SqlCmd
      EXEC (@SqlCmd)
   END
ELSE
   BEGIN
      RAISERROR ('SQL user ''%s'' does not exists in ''sys.database_principals''.', 16, 1, @SQLUserName);
   END