Introduction

In this chapter, we'll introduce the concept of roles. By the end of the chapter, you should have a good understanding of how to use roles to grant access in SQL server. We'll cover how and when to use roles. We'll show you how to create a role and add users to the role via the GUI and via script.

Roles

Roles in SQL Server 2005 are a lot like groups in Windows. Their primary use is to allow an easy method of assigning permissions to a group of users. Roles can have built-in (predefined by SQL Server 2005) or user-defined permissions, and exist at both the server and database level. Most built-in roles cannot be modified, with the exception of the public role at the database level.

At the server level, roles are usually granted to give users some sort of SQL Server 2005 administration permissions. At the database level, roles have two purposes: to allow administration of the database, or to grant specific data or structure permissions inside that database. A role is used to grant certain permissions to SQL logins, SQL roles, Windows logins, or Windows groups. It's important to note that roles can be given other roles as well, making it possible to create roles which group other roles.