SQL Server 2008 R2 Using Command Line to Manage Database Roles
Certain of the T-SQL system stored procedures used in previous versions to achieve roles have been deprecated, including sp_addrole and sp_droprole. The sp_addrolemember and sp_droprolemember processes have not been denounced and are still good selections for adding members to a role. The CREATE ROLE and DROP ROLE statements are the new substitutes 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’DevDbRole1’ N1’chris’
Role membership is not limited to database users. It is possible to assign database roles as members of another role. The following adds the TestDbRole database role to the DevDbRole role created in the previous example:
EXEC sp_addrolemember N’DevDbRole’, N1’TestDbRole’
You cannot use sp_addrolemember to add a fixed database role, a fixed server role, or dbo to a role. You can, however, add a nonfixed database role as a member of a fixed database role. If, for example, you want to add the DevDbRole database role as a member of the fixed database role db_dataread, you use the following command:
EXEC sp_addrolemember Ndb_datareader, NDevDbRole
The ALTER ROLE statement exists but is limited to changing the name of a role. To drop a database role, you use the DROP ROLE statement. Keep in mind that all role members must be dropped before a role can be dropped.