Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?
share|improve this question

1 Answer 1

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/

share|improve this answer
    
I guess you could count Application Roles as a fifth type, though they are really more of a Login/User type. –  RBarryYoung Mar 3 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 at 20:35

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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