create sql user azure sql | GRANT Database Permissions | AZURE sql system catalog views

This article guides to,

create sql user azure sql or AZURE sql system catalog views

When you first deploy Azure SQL, you specify an admin login and an associated password for that login. This administrative account is called Server admin.

SQL Azure does not allow the USE Transact-SQL statement,
which means that you cannot create a single script to execute both the CREATE LOGIN and CREATE USER statements, since those statements need to be executed on different databases.

create sql user azure sql

Create login on master database using below script. Connect azure SQL database slect master database and click on new query

-- add database login on master database
CREATE LOGIN user

WITH PASSWORD = 'password'

Create user on database using below script. In SSMS select user database and click new query

-- add database user for login user
 CREATE USER [user]

FROM LOGIN [user]

WITH DEFAULT_SCHEMA=dbo;

Procedures lists are stored in sys.procedures AZURE sql system catalog views.

If user have db_datareader and db_datawriter still user will not be able to access sys.procedures view. To access those system catalogue view you need to grant specific permission on Azure SQL Database.

If you want list permission on Azure SQL Database, execute below query

SELECT pr.principal_id,pr.name,pr.type_desc,pr.authentication_type_desc, pe.state_desc, pe.permission_name   
FROM sys.database_principals AS pr   
JOIN sys.database_permissions AS pe   
ON pe.grantee_principal_id = pr.principal_id;  

Above query will show list of permission on instance with authentication type. 

To execute sys.procedures need EXECUTE permission to that SQL user.  

GRANT Database Permissions or create sql user azure sql

--Go to SSMS select Azure SQL Database Context and click new query
--This will grant data reader access.
alter role db_datareader add member [user]
--This will grant data writer access.
alter role db_datawriter add member [user]
--If you want to execute sys catalogue view run below commands
GRANT EXECUTE TO [user] 

Post this that user will be able to access AZURE SQL database.