Manage SQL Azure Security

SQL Azure also has a security management system very similar to the SQL on-premises 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 and so on for 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 that has access to all the 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 need to resort to running T-SQL statements.

Note: All of the following procedure are done 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 that 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 to the preceding database. Now you can successfully login to the Azure serer using this login id and password and it will automatically connect to the preceding database.

Mapping to existing database roles

 

There are several pre-defined database roles. You must add the user into any of the roles to provide 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 an entire schema or individual objects can be granted to a entire 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 to the following articles on MSDN:

• CREATE LOGIN
• CREATE USER
• CREATE ROLE

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