Azure SQL - Create User

• Create a local SQL Server authenticated user

CREATE LOGIN user1 WITH PASSWORD = 'Password1';
CREATE LOGIN user1 WITH PASSWORD = 'Password1' -- MUST_CHANGE; (Not supported on Azure SQL DW); 
CREATE USER user1 from LOGIN user1;
exec sp_addRoleMember 'dbmanager', user1;
exec sp_addRoleMember 'loginmanager', user1;

• Create a domain authenticated user

create user [user2@Domain.com] from external provider;
exec sp_addRoleMember 'db_ddladmin', 'user2@domain.com';

• Check current role

Check current roles

SELECT 
   p.NAME rolename,
   m.NAME username
FROM 
   sys.database_role_members rm
JOIN sys.database_principals p
ON rm.role_principal_id = p.principal_id
JOIN sys.database_principals m
ON rm.member_principal_id = m.principal_id

• Grants

GRANT CREATE PROCEDURE TO user1;
GRANT SELECT, INSERT, UPDATE, DELETE, ALTER, EXECUTE, VIEW DEFINITION ON SCHEMA::schema1 TO user1;

• Set Firewall for Azure DB server

SELECT * FROM sys.firewall_rules ORDER BY name;

SELECT * FROM sys.database_firewall_rules ORDER BY name;

EXEC sp_set_database_firewall_rule @name = N'InternaloFirewallRule', 
     @start_ip_address = '0.0.0.0', @end_ip_address = '255.255.255.255';

EXEC sp_delete_database_firewall_rule @name = N'InternaloFirewallRule'

Azure SQL Azure SQL DW