Hierarchical Security

The security environment in Microsoft® SQL Server™ is stored, managed, and enforced through a hierarchical system of users. To simplify the administration of many users, SQL Server uses groups and roles:

• A group is an administrative unit within Microsoft Windows NT® 4.0 and Windows® 2000 that contains Windows NT 4.0 and Windows 2000 users or other groups.
  
  
• A role is an administrative unit within SQL Server that contains SQL Server logins, Windows NT 4.0 and Windows 2000 logins, groups, or other roles.

Arranging users into groups and roles makes it easier to grant or deny permissions to many users at once. The security settings defined for a group are applied to all members of that group. When a group is a member of a higher-level group, all members of the group inherit the security settings of the higher-level group, in addition to the security settings defined for the group itself or user accounts.

The organizational chart of the security system often corresponds to the organizational chart of a company.

These two organizational charts are largely compatible, but there is one common rule for a company's organizational hierarchy that does not apply to the security model: an individual reports only to one manager. This rule implies that an employee can fall into only a single branch of the hierarchical model, as shown in the diagram.

The requirements of a database security system go beyond this one-manager limitation; employees belong to security groups that do not fall within the strict organizational plan of the company. For example, administrative staff exists in every branch of the company and require security permissions regardless of their organizational branch. To support this broader model, the security system in Windows NT 4.0, Windows 2000, and SQL Server allows groups to be defined across a hierarchy. An Administrative group can be created to contain administrative employees for every branch of the company from the Corporate group to the Payroll group.

This hierarchical system of security groups simplifies management of security settings. It allows security settings to be applied collectively to all group members, without having to be defined redundantly for each person. The hierarchical model also accommodates security settings applied only to a single user.