Professional SQL Server™ 2005 Programming

22.4. Server and Database Roles

Prior to version 7.0, SQL Server had the concept of a group—which was a grouping of user rights that you could assign all at once by simply assigning the user to that group. This was quite different from the way Windows groups operated, where a user could belong to more than one Windows group, so you could mix and match them as needed. In SQL Server 6.5 (and earlier) only one user was allowed to belong to one group per database.

The fallout from the pre-SQL Server 7.0 way of doing things was that SQL Server groups fell into one of three categories:

• They were frequently modified by user-level permissions.

• They were only a slight variation off the main group.

• They had more access than required (to make the life of the DBA easier).

Basically, they were one great big hassle, albeit a rather necessary one.

Along came version 7.0, and with it some very big changes. Instead of a group, a user now belongs to a role. A role is, in the most general sense, the same thing as a group.

NOTE

A role is a collection of access rights that can be assigned to a user en masse simply by assigning a user to that role.

The similarities begin to fade there though. With roles, a user can belong to several at one time. This can be incredibly handy since you can group access rights into smaller and more logical groups and then mix and match them into the formula that best fits a user.

Roles fall into two categories:

• Server roles

• Database roles

We'll soon see a third thing that's ...