Adding a Member to a SQL Server Database Role

When you add a new user account in Microsoft® SQL Server™ or change the permissions of an existing user, you can add the user to a SQL Server database role rather than applying permissions directly to the account. Roles can simplify security administration in databases with a large number of users or with a complex security system.

SQL Server users, Microsoft Windows NT® 4.0 or Windows® 2000 users and groups, and other SQL Server database roles all can be added as a member of a role. Because a role is restricted to a single database and cannot be added from one database to another, you can add users, groups, and roles known only to that database.

Note  When you add a Windows NT 4.0 or Windows 2000 login without a user account in the database to a SQL Server database role, SQL Server creates a user account in the database automatically, even if that Windows NT 4.0 or Windows 2000 login cannot otherwise access the database.

A user account can be a member of any number of roles within the same database and can hold permissions appropriate to each role. For example, a SQL Server user can be a member of the admin role and the users role for the same database, with each role granting different permissions. The permission on an object granted to a member of more than one role are the cumulative permissions of the roles. However, a denied permission in one role has precedence over the same permission granted in another role. For example, the admin role may grant access to a table, whereas the users role denies access to the same table. A member of both roles is denied access to the table because denied access is more restrictive and has precedence.

Users to be added to a user-defined database role must already have permission to access the database containing the user-defined role.

To add a member to a SQL Server database role

Transact-SQL