security admin/db_security admin role permission issue

Question

security admin/db_security admin role permission issue

SQLisAwe5oMe

SSChampion

Points: 14047
More actions
August 30, 2017 at 12:30 pm

#325117

Hi Team,
I have a user who has securityadmin server role and db_securityadmin permission on the databases as well.
However, when this user trying creating new user and map to user database, getting the below error. Any idea what causing this.
Error:
Create failed for User 'domain\username'.
Additional Info:
An exception occurred while executing Transact-SQL statement or batch.
User does not have permission to perform this action.(Microsoft SQL Server, Error: 15247)
Regards,
SQLisAwe5oMe.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

SQLisAwE5OmE - Wednesday, August 30, 2017 12:30 PM
Hi Team,
I have a user who has securityadmin server role and db_securityadmin permission on the databases as well.
However, when this user trying creating new user and map to user database, getting the below error. Any idea what causing this.
Error:
Create failed for User 'domain\username'.
Additional Info:
An exception occurred while executing Transact-SQL statement or batch.
User does not have permission to perform this action.(Microsoft SQL Server, Error: 15247)

That user would also need db_accessadmin to add (or remove) access to the database itself.

Sue

SQLisAwe5oMe SSChampion Points: 14047 More actions · Answer 2

Thanks Sue.

So, you are saying a user with securityadmin server role and db_securityadmin db role is not enough to create/add a permission to another user without db_accessadmin privilege ?

Regards,
SQLisAwe5oMe.

Sue_H SSC Guru Points: 90860 More actions · Answer 3

SQLisAwE5OmE - Wednesday, August 30, 2017 1:13 PM
Thanks Sue.
So, you are saying a user with securityadmin server role and db_securityadmin db role is not enough to create/add a permission to another user without db_accessadmin privilege ?

db_accessadmin can add (or remove) users to the database, db_securityadmin cannot. They do two different things.
Whatever it is you want that user to be able to do, you may not not need db_securityadmin and it can be more of a non-intended security risk. Check the Microsoft documentation and make sure to read the specific around what each role can do:
Database-Level Roles

Sue

SQLisAwe5oMe SSChampion Points: 14047 More actions · Answer 4

Sue_H - Wednesday, August 30, 2017 1:25 PM
SQLisAwE5OmE - Wednesday, August 30, 2017 1:13 PM
Thanks Sue.
So, you are saying a user with securityadmin server role and db_securityadmin db role is not enough to create/add a permission to another user without db_accessadmin privilege ?
db_accessadmin can add (or remove) users to the database, db_securityadmin cannot. They do two different things.
Whatever it is you want that user to be able to do, you may not not need db_securityadmin and it can be more of a non-intended security risk. Check the Microsoft documentation and make sure to read the specific around what each role can do:
Database-Level Roles
Sue

Thanks Sue, appreciate it.

Regards,
SQLisAwe5oMe.