SqlCmdScript.sql

SET NOCOUNT ON;
GO

DECLARE @database_name sysname = N'$(DATABASE_NAME)';

IF DB_ID(@database_name) IS NULL
BEGIN
    DECLARE @create_database nvarchar(max) =
        N'CREATE DATABASE ' + QUOTENAME(@database_name) + N';';
    EXEC(@create_database);
END
GO

DECLARE @database_name sysname = N'$(DATABASE_NAME)';
DECLARE @app_user sysname = N'$(APP_USER)';
DECLARE @app_password nvarchar(128) = N'$(APP_PASSWORD)';

IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = @app_user)
BEGIN
    DECLARE @create_login nvarchar(max) =
        N'CREATE LOGIN ' + QUOTENAME(@app_user) +
        N' WITH PASSWORD = N''' + REPLACE(@app_password, N'''', N'''''') + N''', ' +
        N'DEFAULT_DATABASE = ' + QUOTENAME(@database_name) + N', ' +
        N'CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF;';
    EXEC(@create_login);
END
GO

DECLARE @database_name sysname = N'$(DATABASE_NAME)';
DECLARE @app_user sysname = N'$(APP_USER)';

DECLARE @create_user nvarchar(max) =
    N'USE ' + QUOTENAME(@database_name) + N';
    IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = N''' + REPLACE(@app_user, N'''', N'''''') + N''')
    BEGIN
        CREATE USER ' + QUOTENAME(@app_user) + N' FOR LOGIN ' + QUOTENAME(@app_user) + N';
        ALTER USER ' + QUOTENAME(@app_user) + N' WITH DEFAULT_SCHEMA = [dbo];
        ALTER ROLE [db_owner] ADD MEMBER ' + QUOTENAME(@app_user) + N';
    END;';
EXEC(@create_user);
GO