CustomDeploymentRole-DACPAC

DACPAC-CustomSQLRole.sql

-- ============================================================================
-- DACPAC Deployment Role - Complete Setup
-- Purpose: Create reusable role for Azure Pipelines DACPAC deployments
-- Run as: db_owner or sysadmin
-- Target: Each database requiring DACPAC deployments
-- ============================================================================

USE [YourDatabase];  -- CHANGE THIS to your database name
GO

PRINT '====================================================================';
PRINT 'Setting up DACPAC Deployment Role';
PRINT 'Database: ' + DB_NAME();
PRINT 'Date: ' + CONVERT(VARCHAR, GETDATE(), 120);
PRINT '====================================================================';
PRINT '';

-- ============================================================================
-- STEP 1: CREATE ROLE
-- ============================================================================

IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'DacpacDeploymentRole' AND type = 'R')
BEGIN
    CREATE ROLE [DacpacDeploymentRole];
    PRINT '✓ Created role: DacpacDeploymentRole';
END
ELSE
    PRINT '  Role already exists: DacpacDeploymentRole';
GO

-- ============================================================================
-- STEP 2: GRANT PERMISSIONS TO ROLE
-- ============================================================================

-- View permissions (required for schema comparison)
GRANT VIEW DEFINITION TO [DacpacDeploymentRole];
GRANT VIEW ANY DEFINITION TO [DacpacDeploymentRole];

-- Schema permissions
GRANT ALTER ON SCHEMA::dbo TO [DacpacDeploymentRole];

-- Data access (for post-deployment scripts)
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO [DacpacDeploymentRole];
GRANT EXECUTE ON SCHEMA::dbo TO [DacpacDeploymentRole];

-- Object creation permissions
GRANT CREATE TABLE TO [DacpacDeploymentRole];
GRANT CREATE VIEW TO [DacpacDeploymentRole];
GRANT CREATE PROCEDURE TO [DacpacDeploymentRole];
GRANT CREATE FUNCTION TO [DacpacDeploymentRole];
GRANT CREATE TYPE TO [DacpacDeploymentRole];

-- Object modification permissions
GRANT ALTER ANY TABLE TO [DacpacDeploymentRole];
GRANT ALTER ANY VIEW TO [DacpacDeploymentRole];
GRANT ALTER ANY PROCEDURE TO [DacpacDeploymentRole];
GRANT ALTER ANY FUNCTION TO [DacpacDeploymentRole];

PRINT '✓ Granted all necessary permissions to DacpacDeploymentRole';
GO

-- ============================================================================
-- STEP 3: CREATE SERVICE ACCOUNT USER (if not exists)
-- ============================================================================

DECLARE @ServiceAccount NVARCHAR(128) = 'DOMAIN\svc-azurepipelines';  -- CHANGE THIS

IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = @ServiceAccount)
BEGIN
    DECLARE @CreateUserSQL NVARCHAR(500) = 'CREATE USER [' + @ServiceAccount + '] FOR LOGIN [' + @ServiceAccount + '];';
    EXEC sp_executesql @CreateUserSQL;
    PRINT '✓ Created user: ' + @ServiceAccount;
END
ELSE
    PRINT '  User already exists: ' + @ServiceAccount;
GO

-- ============================================================================
-- STEP 4: ADD SERVICE ACCOUNT TO ROLE
-- ============================================================================

DECLARE @ServiceAccount NVARCHAR(128) = 'DOMAIN\svc-azurepipelines';  -- CHANGE THIS

ALTER ROLE [DacpacDeploymentRole] ADD MEMBER [DOMAIN\svc-azurepipelines];
PRINT '✓ Added ' + @ServiceAccount + ' to DacpacDeploymentRole';
GO

-- ============================================================================
-- STEP 5: VERIFICATION
-- ============================================================================

PRINT '';
PRINT 'Verification:';
PRINT '------------------------------------------------------------';

-- Show role permissions
SELECT 
    'Permission' AS Type,
    permission_name AS Name,
    state_desc AS State
FROM sys.database_permissions
WHERE grantee_principal_id = DATABASE_PRINCIPAL_ID('DacpacDeploymentRole')
ORDER BY permission_name;

-- Show role members
SELECT 
    'Member' AS Type,
    dp.name AS Name,
    dp.type_desc AS MemberType
FROM sys.database_principals dp
INNER JOIN sys.database_role_members drm ON dp.principal_id = drm.member_principal_id
WHERE drm.role_principal_id = DATABASE_PRINCIPAL_ID('DacpacDeploymentRole')
ORDER BY dp.name;

PRINT '';
PRINT '====================================================================';
PRINT 'Setup Complete!';
PRINT 'The DacpacDeploymentRole is ready for use.';
PRINT '====================================================================';
GO