Physical Modeling › SQL Server Physical Property Editors › SQL Server Database Objects › SQL Server Principals › Define SQL Server Database Roles

Define SQL Server Database Roles

A role uses Windows security accounts 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. Database roles are not fixed and can be created, 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.

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

To define SQL Server database roles

1. Click Principals, Database Roles on the Database menu.

   The SQL Server 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.

   • 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.

3. Click the Permission tab and work with the following options:

   Note: Click New on the toolbar to create a new permission.

   • 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.

4. Click the Roles tab to select or remove role usage.
5. (Optional) Click the Comment tab and enter any comments that you want to associate with the application role.
6. (Optional) Click the UDP tab to work with user-defined properties for the application role.
7. Click Close.

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