Microsoft® SQL Server 2012 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 were recently deprecated as well and will be removed in a future version of SQL Server.

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:

Click here to view code image

ALTER ROLE DevDbRole ADD MEMBER chris

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