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.
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
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';
-- 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
-- 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