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.

Microsoft SQL Server 2000 Unleashed
Microsoft SQL Server 2000 Unleashed (2nd Edition)
ISBN: 0672324679
EAN: 2147483647
Year: 2002
Pages: 503

Similar book on Amazon © 2008-2017.
If you may any questions please contact us: