-------------------------
-- CREATE USER SCRIPT --
-- v0.2 -----------------

USE [master]

DECLARE @boServiceAccountUsername nvarchar(200)
DECLARE @boServiceAccountPassword nvarchar(200)
DECLARE @SqlStatement nvarchar(200)
DECLARE @CreateDBAccount bit

SET @boServiceAccountUsername = 'boServiceUser'
SET @boServiceAccountPassword = '123'

/* Create server level user account if not exist. */
If NOT EXISTS (SELECT 1 from master.dbo.syslogins WHERE loginname = @boServiceAccountUsername)
BEGIN
    SELECT @SqlStatement = 'CREATE LOGIN [' + @boServiceAccountUsername + '] WITH PASSWORD=''' + @boServiceAccountPassword + ''',
            DEFAULT_DATABASE=[master],
            DEFAULT_LANGUAGE=[us_english],
            CHECK_EXPIRATION=OFF,
            CHECK_POLICY=OFF'

    EXEC sp_executesql @SqlStatement
END

USE [Bocks.Scheduler]

/* Create dabatabse level user account if not exist and add roles to it. */
If NOT EXISTS (SELECT 1 from sys.database_principals WHERE name = @boServiceAccountUsername)
BEGIN
    SELECT @SqlStatement = 'CREATE USER [' + @boServiceAccountUsername + '] FOR LOGIN [' + @boServiceAccountUsername + '] WITH DEFAULT_SCHEMA=[Scheduler]'
    EXEC sp_executesql @SqlStatement

    /* Grant roles to user */
    SELECT @SqlStatement = 'ALTER ROLE [db_datareader] ADD MEMBER [' + @boServiceAccountUsername + ']'
    EXEC sp_executesql @SqlStatement
    SELECT @SqlStatement = 'ALTER ROLE [db_datawriter] ADD MEMBER [' + @boServiceAccountUsername + ']'
    EXEC sp_executesql @SqlStatement
    SELECT @SqlStatement = 'GRANT CONNECT TO [' + @boServiceAccountUsername + '] AS [dbo]'
    EXEC sp_executesql @SqlStatement
END