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