Greg Larsen shows us how to create and use user-defined security roles in SQL Server:
When developing an application that accesses SQL Server, you need to set up a security model, so each user gets the access they need based on their job duties. Some applications require the same access for all users, while other application might require different security access rights based on the role a user has in the organization. The best practice for providing user access is to use role-based security. SQL Server provides user-defined Server and Database roles for defining security rules for different user security needs. This article will discuss how to use user-defined server and database roles to provide secure access to SQL Server resources.
This is something I think DBAs tend to under-utilize. Granted, a big part of why I think DBAs under-utilize it is that we often have Windows groups that we can use as the base for roles, but even so, it’s a good way to secure assets in the database.