Sql Server User vs Role

Question

In Sql Server 2005, what is the difference between a database user account that is mapped to an active directory group vs. a database role that is contains the same active directory group as a member?

Answer 1

A role is a privelege group, whereas a User is a person or Active Directory group. For example, an AD group "IT_Developers" may have Writer access to a database, but the AD group "Domain Administrators" aren't neccessarily admins on the database. There are also different kind of admins, which Active Directory doesn't know about.

Answer 2

In SQLServer, Server logins are mapped to AD users / groups at the server level.

Logins are then mapped to DB users.

Adding db users to roles allows you to set permissions against all the users in the role with a single statement.

For example, if you added all the users that work in customer services to a new role called CustServ, you could write one grant execute script that grants the role (and hence all it's members) execute permission.