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:
ALTER ROLE DevDbRole ADD MEMBER chris
Role membership is not limited to database users. It is possible ...
Get Microsoft SQL Server 2014 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.