848
Part VI: Securing Your SQL Server
Managing Roles with Code
Creating user-defi ned roles with code involves using the sp_addrole system stored(^) procedure. The name can be up to 128 characters and cannot include a backslash, be Null,
or be an empty string. By default, the roles will be owned by the dbo user. However, you
can assign the role an owner by adding a second parameter. The following code creates the
Manager role:
CREATE ROLE [Manager]
Result:
New Manager role added.
The counterpart of creating a role is removing it. A role may not be dropped if any users are
currently assigned to it. The DROP ROLE command removes the role from the database:
DROP ROLE [Manager]
Result:
Manager role dropped.
sp_addRoleMember and sp_dropRoleMember are deprecated features. Microsoft advises using ALTER
instead.
After a role has been created, users may be assigned to the role by means of the ALTER ROLE
command. The following code assigns the Windows login for Jose to the Manager role:
ALTER ROLE [Manager] ADD MEMBER [AgileBay\Jose]
Result:
The Windows login [AgileBay\Jose] added to the Manager role.
The ALTER ROLE command is also used to remove a user from an assigned role. The fol-
lowing code removed Jose from the Manager role:
ALTER ROLE [Manager] DROP MEMBER [AgileBay\Jose];Result:
The Windows login [AgileBay\Jose] removed from the Manager role.
Hierarchical Role Structures
If the security structure is complex, then a powerful permission-organization technique is
to design a hierarchical structure of user-defi ned database roles. In other words, you can
nest user-defi ned database roles.
■ (^) The worker role may have limited access.
c33.indd 848c33.indd 848 7/31/2012 10:01:25 AM7/31/2012 10:01:25 AM
http://www.it-ebooks.info