Azure SQL - Create logins and users

create-azure-sql-logins-and-users.sql

-- For more useful info on permissions, see:
--     https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-database-role-members-transact-sql?view=sqlallproducts-allversions

-- Create logins and users on Azure SQL databases.
-- Adjust roles and schemas appropriately
DECLARE @user   varchar(255) = 'USERNAME';
DECLARE @pass   varchar(255) = 'PASSWORD';
DECLARE @schema varchar(255) = '[dbo]';

IF ((SELECT DB_NAME()) = 'master')
    BEGIN
        PRINT 'Running on MASTER';
        PRINT 'Creating login and user ' + @user + '...';

        -- Note, creating a user in master is useful as most tools such as SSMS will
        -- connnect to master by default to present a list of databases to the user.
        -- If you don't want to leak/reveal the databases on the server, you should
        -- only create a login. Don't create a user in master.
        DECLARE @create_login_cmd varchar(max) = '
            CREATE LOGIN ' + @user + ' WITH password=''' + @pass + ''';
            CREATE USER ' + @user + ' FOR LOGIN ' + @user + ' WITH DEFAULT_SCHEMA=[dbo];
        ';
        EXEC (@create_login_cmd);
        PRINT 'Done';
    END
ELSE
    BEGIN
        PRINT 'NOT running on MASTER';
        -- Create user in the relevant database
        PRINT 'Creating user ' + @user + ' with schema ' + @schema + '...';
        DECLARE @create_user_cmd varchar(max) = '
            CREATE USER ' + @user + ' FOR LOGIN ' + @user + ' WITH DEFAULT_SCHEMA=' + @schema + ';
        ';
        EXEC (@create_user_cmd);
        PRINT 'Done';

        PRINT 'Adding user to roles...';
        EXEC sp_addrolemember 'db_datareader', @user;
        --EXEC sp_addrolemember 'db_datawriter', @user;
        PRINT 'Done';

        PRINT 'Granting specific permissions...';
        -- If required
        --DECLARE @grant_on_schema_cmd varchar(max) = '
        --	GRANT EXECUTE ON SCHEMA :: ' + @schema + ' TO ' + @user + ';
        --';
        --EXEC (@grant_on_schema_cmd);
        PRINT 'Done';
    END