Microsoft® SQL Server 2008 R2 Unleashed

Using T-SQL to Manage Database Roles

Some of the T-SQL system stored procedures used in prior versions to manage roles have been deprecated, including sp_addrole and sp_droprole. The sp_addrolemember and sp_droprolemember procedures have not been deprecated and are still good choices for adding members to a role.

The CREATE ROLE and DROP ROLE statements are the new replacements for sp_addrole and sp_droprole. The following example uses the CREATE ROLE statement to create a new database role named DevDbRole:

CREATE ROLE [DevDbRole]

To assign a user named Chris to the new DevDbRole role, you can use the following:

EXEC sp_addrolemember N'DevDbRole', N'chris'

Role membership is not limited to database users. It is possible to assign database roles ...