Adding a Member to a SQL Server Database Role

When you add a new user account in Microsoft® SQL Server™, or you need to 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. SQL Server users, Microsoft Windows NT® users, Windows NT 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, you can add users, groups, and roles known only to that database. It is not possible to add users, groups, or roles from one database to a role in another database. Roles can simplify security administration in databases with a large number of users or with a complex security system.

Note When you add a Windows NT login without a user account in the database to a SQL Server database role, SQL Server creates a user account in the database automatically.

A user account can be a member of any number of roles within the same database. 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 effective permissions on an object granted to a member of more than one role are the cumulative permissions of the roles, although 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 the most restrictive.

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