Creating User-Defined SQL Server Database Roles

Create Microsoft® SQL Server™ database roles when a group of users needs to perform a specified set of activities in SQL Server and one of the following is true:

• There is no applicable Microsoft Windows NT® 4.0 or Windows® 2000 group.
  
  
• You do not have permissions to manage Windows NT 4.0 or Windows 2000 user accounts.

Note  Avoid deep levels of nested roles because this can affect performance.

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 4.0 or Windows 2000 group that includes just these employees, and there is no other reason to create one in Windows NT 4.0 or Windows 2000. A custom SQL Server database role, CharityEvent, can be created for this project and individual Windows NT 4.0 and Windows 2000 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 the CharityEvent users 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:

• For any user, more than one database role can be active at any time.
  
  
• SQL Server roles can contain Windows NT 4.0 or Windows 2000 groups and users and SQL Server users and other roles, provided that all users, groups, and roles exist in the current database.
  
  
• A user can belong to more than one role in the same database.
  
  
• A scalable model is provided for setting up the correct level of security within a database.

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 by different users cannot be created.

To create a SQL Server database role

Transact-SQL