security admin/db_security admin role permission issue

• SQLisAwe5oMe

  SSChampion

  Points: 14052

  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.

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  August 30, 2017 at 1:05 pm

  #1957540

  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: 14052

  More actions

  August 30, 2017 at 1:13 pm

  #1957541

  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

  August 30, 2017 at 1:25 pm

  #1957542

  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: 14052

  More actions

  August 30, 2017 at 1:32 pm

  #1957546

  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.