Click here to Skip to main content
15,561,817 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 9: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[^]
 
Share this answer
 
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.

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