Click here to Skip to main content
14,979,508 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
C#
Hi all.
I’m using SQL Server 2014.
I have a question about database roles.
why does the user have to be in db_owner role to use db_securityadmin role permissions?
db_owner can grant to alter any role individually.

regards.


What I have tried:

it's just a question about db_securityadmin!
Posted
Updated 14-Sep-16 8:09am
Comments
Richard Deeming 14-Sep-16 9:15am
   
Your question makes no sense. If the user is in the db_securityadmin role, they have the permissions associated with that role. They don't need to be in the db_owner role as well.

There are some things that db_securityadmin can't do, but that's a different issue.
Pouria Polouk 14-Sep-16 12:35pm
   
Yes, I understand.
But when I want to use some of db_securityadmin role permissions I have to be in db_owner role either.
For example in the code below:

EXEC sp_addrolemember 'db_datareader','otherUser'

When I wanna run it, just db_securityadmin role is not enough and I have to be in db_owner too. On the other hand when I am in db_owner role, I’ll have the access to higher permissions’ level uncontrollably, which this must NOT happen!

what's the solution?
Richard Deeming 14-Sep-16 12:42pm
   
Adding members to a fixed role is one of the permissions that is not granted to the db_securityadmin role.

It's clearly mentioned in the documentation:

sp_addrolemember[^]:
Adding members to fixed database roles requires membership in the db_owner fixed database role.

ALTER ROLE[^]:
Additionally, to change the membership in a fixed database role you need: Membership in the db_owner fixed database role

So the "solution" is to either add the user who's managing permissions to the db_owner role, or to use custom roles instead of the fixed database roles.

NB: If this restriction wasn't in place, there would be nothing to stop the db_securityadmin user from adding themselves to the db_owner fixed role.
Pouria Polouk 14-Sep-16 13:07pm
   
Unfortunately I hadn't studied the documentation.
Thank you so much Richard.
Solved.

1 solution

I'd strongly recommend to start with basics: buy a book or find an electronic version of that in the net.

Most important persmissions of db_owner and db_securityadmin has been listed here:

db_owner db_securityadmin

  1. The db_owner role allows a user to do anything within the database.
  2. DBAs who are already members of the sysadmin fixed server role come in as dbo and don't need this role explicitly granted to them.
  3. Normal users should not be a member of this role.
  4. Applications might require their user account to be a member of this role.


  1. The db_securityadmin role can manage role membership and permissions on securables.
  2. Again, since DBAs usually manage security and are usually coming in as dbo, this role is little used.
  3. Normal users should not be a member of this role.
  4. Applications should tend not to need this role.
  5. Since it's little used, you should audit its membership for exceptions.


Source: Understanding SQL Server fixed database roles[^]

For further details, please see:
Permissions of Fixed Database Roles (Database Engine)[^]
Permissions of Fixed Server Roles (Database Engine)[^]
Server and Database Roles in SQL Server[^]
   
Comments
Pouria Polouk 15-Sep-16 4:44am
   
thank you dear friend
Maciej Los 15-Sep-16 5:33am
   
You're very welcome.
Cheers, Maciej
Pouria Polouk 15-Sep-16 14:05pm
   
May I ask a question?
Maciej Los 15-Sep-16 14:07pm
   
Of course!
Pouria Polouk 15-Sep-16 14:38pm
   
Could you tell me if you know a perfect resource of examples about "grant alter"?
I think that Microsoft's samples aren't enough.
Pouria Polouk 15-Sep-16 14:47pm
   
yes, But it's not enough.
I want more examples.
Maciej Los 15-Sep-16 15:25pm
   
Well, you have to make some researches (use Google) or buy a book... Sorry.
Pouria Polouk 15-Sep-16 17:27pm
   
thank you for your help
Maciej Los 16-Sep-16 2:57am
   
You're very welcome.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900