Create a SQL login, connect a user to it, and add roles to the user

create-sql-login-user.sql

-- Source: https://ainab.site/2021/12/08/create-an-azure-sql-server-login-and-connect-a-user-to-it/
-- Execute this query in the "master" database

-- Create new login
CREATE LOGIN [new-login]
    WITH PASSWORD = 'complex-password'
GO

-- Execute these queries in the target database

-- Create a user and connect it to the login
CREATE USER [new-user] FOR LOGIN [new-login]

-- Create a role to execute stored procedures
CREATE ROLE [db_executor] AUTHORIZATION [dbo]
GO

GRANT EXECUTE TO [db_executor]
GO

-- Give the user read/write/execute rights
sp_addrolemember @rolename = 'tc_execute', @membername = 'new-user'
GO
sp_addrolemember @rolename = 'db_datareader', @membername = 'new-user'
GO
sp_addrolemember @rolename = 'db_datawriter', @membername = 'new-user'
GO

-- Check if roles have been assigned to the user correctly
    SELECT 
    UserType='Role', 
    DatabaseUserName = '{Role Members}',
    LoginName = DP2.name,
    Role = DP1.name,
    'SELECT' AS [PermissionType] ,
    [PermissionState]  = 'GRANT',
    [ObjectType] = 'Table',
    [Schema] = 'dbo',
    [ObjectName] = 'All Tables',
    [ColumnName] = NULL
FROM sys.database_role_members AS DRM  
RIGHT OUTER JOIN sys.database_principals AS DP1  
    ON DRM.role_principal_id = DP1.principal_id  
LEFT OUTER JOIN sys.database_principals AS DP2  
    ON DRM.member_principal_id = DP2.principal_id  
WHERE DP1.type = 'R'
AND DP2.name IS NOT NULL