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 ...
Get Microsoft® SQL Server 2008 R2 Unleashed now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.