Db_securityadmin role in SQL server

Question

0.00/5 (No votes)

See more:

SQL-Server-2014

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 14-Sep-16 0:12am

Pouria Polouk

Updated 14-Sep-16 8:09am

Add a Solution

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

Add a Solution

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

Maciej Los · Accepted Answer · 2016-09-14T08:09:00

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
The db_owner role allows a user to do anything within the database. 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. Normal users should not be a member of this role. Applications might require their user account to be a member of this role.	The db_securityadmin role can manage role membership and permissions on securables. Again, since DBAs usually manage security and are usually coming in as dbo, this role is little used. Normal users should not be a member of this role. Applications should tend not to need this role. 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[^]

Db_securityadmin role in SQL server

1 solution

Solution 1

Add your solution here

Preview 0

Db_securityadmin role in SQL server

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us