To create a SQL Server database roleWhen a group of users needs to perform a specified set of activities in Microsoft® SQL Server™ and there is no applicable Microsoft Windows NT® group, or if you do not have permissions to manage Windows NT user accounts, you can add a SQL Server role in the database.
For example, a company may form a Charity Event Committee involving employees from different departments and from several different levels in the organization. These employees need access to a special project table in the database. There is no existing Windows NT group that includes just these employees, and there is no other reason to create one in Windows NT. A custom SQL Server database role, CharityEvent, could be created for this project and individual Windows NT users added to the database role. When permissions are applied, the users in the database role gain table access. Permissions for other database activities are not affected, and they are the only ones who can work with the project table.
SQL Server roles exist within a database and cannot span more than one database.
The advantages of using database roles include:
Because users can belong to more than one database role at a time, it is not necessary for users to assume the identity (and permissions) of other users through temporary aliases.
Note A database role is owned by either the user explicitly specified as the owner when the role is created, or the user who created the role when no owner is specified. The owner of the role determines who can be added or removed from the role. However, because a role is not a database object, multiple roles of the same name in the same database owned and by different users cannot be created.
To create a SQL Server database role
Backward Compatibility Details (Level 4)