Create SQL Login for SQL Azure Database

Generating Logins

Logins are server wide login and password pairs, where the login has the same password across all databases. Here is some sample Transact-SQL that creates a login:

-- master
CREATE LOGIN usrUser01 WITH password='12345';
GO

You must be connected to the master database on SQL Azure with the administrative login (which you get from the SQL Azure portal) to execute the CREATE LOGIN command. Some of the common SQL Server logins can be used like sa, Admin, root, for a complete list click here.

Creating Users

Users are created per database and are associated with logins. You must be connected to the database in where you want to create the user. In most cases, this is not the master database. Here is some sample Transact-SQL that creates a user:

-- Specific Database
CREATE USER usrUser01 FROM LOGIN usrUser01;
GO

User Permissions

Just creating the user does not give them permissions to the database. You have to grant them access. In the Transact-SQL example below the usrUser01 is given read only permissions to the database via the db_datareader role.

EXEC sp_addrolemember 'db_datareader', 'usrUser01';

Database Roles

-- Create the database role
CREATE ROLE Role01 AUTHORIZATION [dbo]
GO

-- Add an existing user to the role
EXEC sp_addrolemember N'Role01', N'usrUser01'
GO

Grant Execute Stored Procedure to Role

GRANT EXECUTE ON OBJECT::dbo.uspMySP01 TO Role01; 
GO 

Example

-- master
CREATE LOGIN usrFunction WITH PASSWORD = 'xxxxxxx';
GO

-- Specific Database
CREATE USER usrFunction FROM LOGIN usrFunction;
GO

EXEC sp_addrolemember 'db_datareader', 'usrFunction';
GO
EXEC sp_addrolemember 'db_datawriter', 'usrFunction';
GO

-- Create the database role
CREATE ROLE roleFunction AUTHORIZATION [dbo]
GO

-- Add an existing user to the role
EXEC sp_addrolemember N'roleFunction', N'usrFunction'
GO

GRANT EXECUTE ON OBJECT::dbo.uspXXXXXXXX TO roleFunction; 
GO 

Sources:

https://azure.microsoft.com/en-us/blog/adding-users-to-your-sql-azure-database/