How To: Manage SQL Azure Security - Logins, Users, Roles, Schemas and Permissions

SQL Azure too has its security management system very similar to the SQL on-premise versions. It bascially consists of the following:

1. Logins - Server level
2. Users - Database level, mapped to server logins
3. Schemas - Database level, authorized/owned by a user or another schema
4. Roles - Database level, authorized/owned  by a user or another role
5. Permissions - Database level, permission like SELECT, DELETE, ALTER etc. on objects/schemas granted to users/roles

Let's take a closer look  at each of these.

When you create a server in SQL Azure, it asks you to create a login at the same time.  That login acts as the administrative login, which has access to all databases in that server. However, you might want to create other logins with less privileges. As of now, the SQL Azure portal doesn't have any UI to create these extra logins. So you'll have to resort to running T-SQL statements.

Note: All of the below steps are performed using the administrative login mentioned above.

Creating Logins
Login to the master database and run the following t-sql statement.

CREATE LOGIN MyServerLogin WITH password='My#Password123'

This statement creates a login ID in the server. This a a normal login ID, which doesn't have access to any of the databases in that server. So if you try to login to the azure server with this account (either from SQL Server Management Studio 2008 R2 - SSMS  OR from Azure Portal) you would get an error saying this user doesn't have access to the master database. So the next step is to map this login to the required databases, not necessarily to the master db.

Mapping to Databases
Login to the required database where you want to grant access to the new login and run the following statement.

CREATE USER MyDatabaseUser FROM LOGIN MyServerLogin;

Now this login ID "MyServerLogin" has 'public' access the above database. Now you can successfully login to the azure serer using this login id and password and it will automatically connect to the above database.

Mapping to existing database roles
There are several pre-defined database roles. You have to add the user into any of the roles to give the user sufficient permissions to operate on the database.
 

EXEC sp_addrolemember 'db_datareader', 'MyDatabaseUser';

Creating custom roles
If none of the pre-defined roles suit your purpose, then you can create custom roles and grant hand-picked permissions to it.

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

Granting permissions to roles
Permissions to perform operations on a whole schema or individual objects can be granted to a whole role or a specific user.

Here in this example we are giving all possible permissions on the DBO schema (All database objects belong to this schema normally. However, custom schemas can be very well created)

-- Grant access rights to a specific schema in the database
GRANT
      ALTER,
      CONTROL,
      DELETE,
      EXECUTE,
      INSERT,
      REFERENCES,
      SELECT,
      TAKE OWNERSHIP,
      UPDATE,
      VIEW DEFINITION
ON SCHEMA::dbo
      TO MyDbRole
GO

-- Add an existing user to the new role created
EXEC sp_addrolemember 'MyDBRole', 'MyDBUser'
GO

Revoking permissions from roles
Permissions once given can be revoked from it as well using the DENY statement:

-- Revoke access rights on a schema from a role
DENY ALTER
ON SCHEMA::dbo
      TO MyDbRole

For more information on the T-SQL syntax, refer the below articles on MSDN:

CREATE LOGIN
CREATE USER
CREATE ROLE

Hope this helps to understand the fundamental security aspects of SQL Azure!