Managing Database Roles

Besides adding logins and users to fixed server and database roles, managing roles involves creating roles to organize your users, and group application permissions.

Using Enterprise Manager

In Enterprise Manager, database roles are created in the Roles folder of the database. After roles are created, they are managed from the Property page of the role. To create a role, right-click Roles, and select New Database Role, as shown in Figure 15.8.

Figure 15.8. The New Database Role dialog box.

Enter a name for the role. If it is a standard role, you can add users to the role at this time if you want. If you select to create an application role, provide a password. Click OK and you are finished. As when creating users, permissions can't be applied until after the role is created.

Using T-SQL

Four stored procedures are used to manage roles with T-SQL. Table 15.4 lists the role-stored procedures.

Table 15.4. Stored Procedures for Managing Roles

Stored Procedure	Function
sp_addrole	Add a SQL Server role
sp_droprole	Drop a SQL Server role
sp_addrolemember	Add a SQL Server user to a role
sp_droprolemember	Drop a SQL Server user from a role

The syntax for adding a role called 'HR_Role', owned by 'dbo', is as follows :

 EXEC sp_addrole 'HR_Role', 'dbo' 

If you then wanted to add a user to that role, you could execute the following:

 EXEC sp_addrolemember 'HR_Role', 'bobw' 

The syntax for removing a role member and dropping the role is illustrated here:

 EXEC sp_droprolemember 'HR_Role', 'bobw'  GO EXEC sp_droprole 'HR_Role' 

All role members must be dropped before the role can be dropped.