Roles, Users and Role Members

Question

A role is "owned" by a user.

Users can be "Role Members" of a role as well.

Could someone help me understand the difference and applications of these two concepts?

Answer 1

Hi James,

Hope you wanted to understand the difference between Roles, Users and Role Members. Let us have an example to have more understanding.

User
Smith, Bob, James are examples of the users who can access the SQL Server.

Roles
We have different type of Roles in SQL server eg: Sysadmin-The members of sysadmin server role can perform any activity in SQL Server and have completes control over all database functions.
Smith and James plays a role of Sysadmin on SQL server Box.
Bob plays a role of db_datareader on a particular database.

Role Memeber
Who are all members belongs to a particular role is called as a role members.
Example:-List the members who has db_datareader role Answer will be Smithm, Bob & James (Smith & James are sysadmin, So they have all the access on the SQL server Instance).
Example:-List the members who has Sysadmin role Answer will be Smithm & James (Bob has only datareader role so he can't make any changes, where sysadmin can do anything on the server).

Note:-To know more info about different type of roles in SQl Server click here

Thanks & Regards,
pramialrani.R

Answer 2

In a large organization Roles make is easier to manage permissions. For example, a Manager role might have all the managers in the organization. The access permission to the general management reports is granted to the Manager role. All users that are managers are added to the Manager role and they automatically receive all the required permissions to the general management reports. But somebody has to keep the Manager role up-to-date with new permissions and new members. So one user or (another) role gets the ownership of the Manager role, so they can do the maintenance. (A role can be owned by an individual user or another role.) In many cases roles are owned by the dbo user. And anyone who is a sysadmin or member of the db_owner role enters the database as the dbo user.

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

Answer 3

Pramialrani gave a good description of Users, Roles, and Role Members.  Let me add something about Role Owners.

Role Owner - An administrative feature

A user-defined database Role can only have one user as its owner.  When a role is created, either a Role Owner can be assigned (assuming enough privileges) or it will default to the creator of the role.   The owner of the role is one of the database principals that can manage Role Membership, along with db_owner, db_securityadmin, and anyone granted ALTER ROLE permissions on the role.

The database owner can be a user that maps to a Windows Group that is defined as a login on the server.  Therefore, many individual Windows Logins could be owner (via the one user) of the Role.

Role Member - An assignment that contols a grouping of security in the database

Role Members in a role can be a practically infinite number, when you consider that Windows Groups can be mapped as users of the database and the Groups on your domain could contain other groups, et cetera.  All receiver rights from roles, at least public, and any rights directly granted.  (Good policy: only grant and revoke rights through roles instead of directly to individual users.) 

Analogy: 

A database has a single owner (often 'sa', but it can be any login) that maps internally to the database user 'dbo' and 'dbo' is a db_owner of the database.  The database ownership (not the db_owner role) also has an additional power for restore that you can read about here: http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-security/4759/Minimum-permissions-to-restore-a-database

A database has many users (i.e. "members" or "database principals") that must be made members of roles.  A database can also have a very large number of users and roles.  And on we go ...

RLF

 

 

 

Answer 4

I didn't know that the purpose of the "owner" of a role is managing the role.  Thanks everyone for clearing it up.