Create_DB_With_User.sql

-- =========================================
-- Idempotent Setup-Script for DB + User with db_owner-rights
-- =========================================
SET NOCOUNT ON;

-- Variables
DECLARE @DbName     sysname        = N'DB Name';
DECLARE @UserName   sysname        = N'Username';
DECLARE @Password   nvarchar(128)  = N'Secret Password';

-- Utiliy functions: secure quoting for identifiers and string-literals
DECLARE @DbNameQ       nvarchar(258) = QUOTENAME(@DbName);
DECLARE @UserNameQ     nvarchar(258) = QUOTENAME(@UserName);
DECLARE @PasswordLit   nvarchar(300) = N'''' + REPLACE(@Password, '''', '''''') + N''''; -- 'pass' -> 'pass' mit Escapes

------------------------------------------------------------
-- 1) Crate DB (if not exists)
------------------------------------------------------------
IF DB_ID(@DbName) IS NULL
BEGIN
    DECLARE @sqlCreateDb nvarchar(max) =
        N'CREATE DATABASE ' + @DbNameQ + N';';
    EXEC(@sqlCreateDb);
END

------------------------------------------------------------
-- 2) Create SQL-Login (if not exists)
--    Only for SQL-Logins (sys.sql_logins), nott Windows-Logins
------------------------------------------------------------
IF NOT EXISTS (SELECT 1 FROM sys.sql_logins WHERE name = @UserName)
BEGIN
    DECLARE @sqlCreateLogin nvarchar(max) =
        N'CREATE LOGIN ' + @UsernameQ + N' WITH PASSWORD = ' + @PasswordLit + N', CHECK_POLICY = ON, CHECK_EXPIRATION = OFF;';
    EXEC(@sqlCreateLogin);
END

------------------------------------------------------------
-- 3) Set Default-DB for login
------------------------------------------------------------
DECLARE @sqlSetDefault nvarchar(max) =
    N'ALTER LOGIN ' + @UsernameQ + N' WITH DEFAULT_DATABASE = ' + @DbNameQ + N';';
EXEC(@sqlSetDefault);

------------------------------------------------------------
-- 4) Creat/Assign User in DB as dbowner
------------------------------------------------------------
DECLARE @sqlUserAndRole nvarchar(max) =
N'USE ' + @DbNameQ + N';
IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = N''' + REPLACE(@UserName, '''', '''''') + N''')
BEGIN
    CREATE USER ' + @UserNameQ + N' FOR LOGIN ' + @UsernameQ + N';
END
ELSE
BEGIN
    -- Orphan-Fall: User exists, but SID does not match login -> reassign
    IF (SELECT sid FROM sys.database_principals WHERE name = N''' + REPLACE(@UserName, '''', '''''') + N''')
       <> SUSER_SID(N''' + REPLACE(@Username, '''', '''''') + N''')
    BEGIN
        ALTER USER ' + @UserNameQ + N' WITH LOGIN = ' + @UsernameQ + N';
    END
END

-- assign db_owner-membership only if not exists
IF NOT EXISTS (
    SELECT 1
    FROM sys.database_role_members drm
    JOIN sys.database_principals r ON r.principal_id = drm.role_principal_id AND r.name = N''db_owner''
    JOIN sys.database_principals u ON u.principal_id = drm.member_principal_id AND u.name = N''' + REPLACE(@UserName, '''', '''''') + N'''
)
BEGIN
    ALTER ROLE db_owner ADD MEMBER ' + @UserNameQ + N';
END
';
EXEC(@sqlUserAndRole);