Creating Models › Target Servers for Physical Models › Database Property Editors › SQL Azure Property Editors › SQL Azure Database Support in CA ERwin Data Modeler › Define SQL Azure Database Roles

Define SQL Azure Database Roles

A role is used to limit the scope of access and permissions when users access the database. A database role specifies a set of database level permissions that let a user perform specific tasks. SQL Azure has fixed database roles (such as db_owner) that cannot be modified, however, you can create database roles that can be modified and removed. Database roles let you collect users into a single unit to which you can apply permissions. You can define a set of roles based on job functions and assign each role the permissions that apply to that job.

In SQL Azure, there are two Master Database roles used for creating databases and logins.

dbmanager

The dbmanager role is the server-level role that is required for creating databases. Only the server-level principal login or other logins that have the dbmanager role can create databases. Once a login has the dbmanager role, it can create a database with the SQL Azure CREATE DATABASE command, but that command must be executed in the Master Database.

loginmanager

The loginmanager role is the server-level security role that is required for creating logins. Only the server-level principal login or other logins that have the loginmanager role can create other logins.

Use the SQL Azure Database Role Editor to define database role objects in a SQL Azure physical model.

To define SQL Azure database roles

1. Click Target - SQL Azure, Principals, Database Roles on the Model menu.

   The SQL Azure Database Role Editor opens.

2. Select the database role in the Navigation Grid that you want to define and work with the following options:

   Note: Click New on the toolbar to create a new database role. Use the Enter filter text box to filter a very large list of database roles to quickly locate the one that you want to define.

   Name

   Displays the database role name. You can change the database role name in this field.

   Authorization

   Specifies the authorization level for the database role. Select an authorization from the drop-down list.

   Generate

   Generates SQL during forward engineering. Clear the check box if you do not want to generate SQL.

3. Click the Roles tab to select or remove role usage.
4. Click the Permission tab and work with the following options:

   Note: Use the options on the toolbar to create a new permission, delete a selected permission, or open the SQL Azure Permission Editor to manage permission properties.

   Name

   Displays the permission name. Change the name of the permission in this field.

   Type

   Specifies the permission type. Select the option from the drop-down list.

   Cascade

   Specifies the cascade action for the permission. Indicates that the permission revoked is also revoked from other principals to which it has been granted by this principal.

   With Grant

   Specifies if the user can grant this permission to other users or not.

   Grant As Principal

   Specifies the principal used to acquire the permissions. Select a principal from the drop-down list.

5. (Optional) Click the Comment tab and enter any comments that you want to associate with the database role.
6. (Optional) Click the UDP tab to work with user-defined properties for the database role.
7. (Optional) Click the Notes tab to view history information and view or edit user notes.
8. Click Close.

   The database role is defined and the SQL Azure Database Role Editor closes.

More information:

Define Permissions for an Object in a SQL Azure Property Editor

Add a Comment in a Property Editor

Add a UDP in a Physical Property Editor

Edit User Notes or View Object History