Privileges for Application/Functional account in database

• SQL!$@w$0ME

  SSChampion

  Points: 12349

  More actions

  January 19, 2016 at 8:57 am

  #324178

  Hi Guys,

  What rights/privileges should I assign in the database for the functional account. Please share script to create userdefined db role if that is a best practice.

  Many thanks.

• Phil Parkin

  SSC Guru

  Points: 247158

  More actions

  January 19, 2016 at 9:12 am

  #1852820

  --Create the role

  create role myApp authorization dbo;

  go

  --Create users in the db

  --Add members to the role

  alter role myApp

  add member [domain\user];

  --etc

  --Grant permissions to the role

  grant exec on schema::mySchema to myApp

  --etc etc

  ---

• SQL!$@w$0ME

  SSChampion

  Points: 12349

  More actions

  January 19, 2016 at 9:29 am

  #1852823

  Thanks Phil. Please advise if the following permissions are good enough.

  data reader, data writer, Create role for View/Execute permissions on db level (dbo and all user defined schemas).

• Phil Parkin

  SSC Guru

  Points: 247158

  More actions

  January 19, 2016 at 9:44 am

  #1852827

  SQL!$@w$0ME (1/19/2016)

  ---

  Thanks Phil. Please advise if the following permissions are good enough.

  data reader, data writer, Create role for View/Execute permissions on db level (dbo and all user defined schemas).

  I cannot advise on this. The permissions required for your application will be specific to your application.

  ---