General Guidelines:

  1. Use Windows authentication whenever possible for all individual user connections and application accounts
  2. Individual Windows accounts should be granted database access via AD group rather than as a stand-alone account (see application AD groups section below)
  3. Object changes (DDL) should be executed using a Windows administrative account (Admin ID) which is separate from HawkID or application service account
    • Each developer should have an individual Admin ID that will be added to the db_admin database role via AD group

Application Service Accounts:

  1. Native SQL Server accounts should only be used in applications where Windows authentication is not supported
  2. Accounts should be unique to each application or solution
    • Separate application accounts on production and non-production are suggested for data sources classified as critical or restricted
    • Do not include server name in account name
    • Append environment type to end of account name (e.g. -Test or _Test for test environment)
  3. Application service accounts should NOT have full administrative privileges (i.e. the ability to modify all objects or permissions)
    • If limited DDL changes are required for application functionality, grant elevated access only to the objects that require it
  4. Use complex passwords or passphrases
    • Update passwords or passphrases when a staff member leaves the workgroup

Application AD Groups:

Each application should have at least two AD groups

  1. Elevated privileges AD group
    • Usually given the db_admin database role with the following permissions
      • DML: read, write, and execute
      • DDL: create, alter, and drop objects
      • Security: grant and remove object permissions and role membership
    • Should be restricted to individual Admin IDs
    • NO HawkIDs and NO application service accounts should be given membership
  2. Standard access group(s)
    • Create a unique group for each application or solution
    • Usually read-only or read-write, may also include execute
    • Contains application service accounts
    • May also contain HawkIDs 
    • Only create AD groups needed for application functionality 
  3. Group best practices
    • DO NOT nest groups inside these groups
    • DO NOT include server network name in group name
    • To differentiate between production and non-production environments, append environment to the end of the group name (e.g. -Prod -Test -Dev -QA)

SQL Server Agent Accounts:

  1. Windows service account to manage SQL Server Agent jobs and schedules – usually one per workgroup
  2. This account must be added to SQLAgentUserRole on a server
  3. The account may also be added to DatabaseMailUserRole as needed

What UIOWA database roles can be requested?

  • db_admin
  • db_ui_datareader
  • db_ui_datawriter
  • db_ui_ddladmin
  • db_ui_grantexecute
  • db_ui_viewdefinition

What naming convention should I use for a SQL Server account?

Service Accounts:

  • Windows
    • <<Group>>-<<AppName>>-AppUser  
    • <<Group>>-SQL-Jobs
  • SQL Server
    • <<Group>>_<<AppName>>_ AppUser  

AD Groups:

  • <<Group>>-<<AppName>>-DbAdmin 
  • <<Group>>-<<AppName>>-DbRO
  • <<Group>>-<<AppName>>-DbRW
  • <<Group>>-<<AppName>>-DbExec
Last updated
Article number
8306