4

I have SQL Server 2012. When I create a user in "Owned schemas" screen one of the option is fixed database role. If I own a fixed database role:

  1. What permissions am I granted?
  2. I am the admin of this role?
5

SQL Server knows four types of roles:

  • fixed server roles
  • fixed database roles
  • server roles
  • database roles

The fixed roles are system supplied and you should not change them. In fact trying to change the owner on a fixed server role aborts the connection with a level 20 error.


For the non-fixed roles the following applies:

The owner of a securable has the CONTROL privilege on that securable. That is true for any securable, including databases, schemata, procedures and roles.

In the case of a role there is no additional privilege that is implied. In particular, the role owner is not automatically a member. The role owner also does not have any permissions on the other role members.

What the CONTROL privilege does imply is the ability to add and remove members from the role, change the owner on the role and drop the role.

Keep in mind however that the role owner can add themselves as a member to that role and therefore can easily acquire the permissions granted to the role itself.

This all is true for both server roles and database roles.


I just wrote a blog post about this with a little more detail: http://sqlity.net/en/2169/role-owner/

  • I guess you could count Application Roles as a fifth type, though they are really more of a Login/User type. – RBarryYoung Mar 3 '14 at 18:48
  • @RBarryYoung, you are right, they are also called roles. But they are not used to group other principals together, so they don't really apply here. – Sebastian Meine Mar 3 '14 at 20:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.