SQL rights are needed for the Application Identity, service account, or SQL account

Products

• IT Management Suite
• Client Management Suite

Issue/Introduction

What SQL rights or database permissions are needed for the Application Identity (App ID), service account, or SQL account?  

Environment

ITMS 8.x

Resolution

NOTE: The following content applies to Notification Server or Symantec Management Platform versions 8.x and later versions.

Minimum SQL Security Requirements

The account that Notification Server uses to access the SQL database needs to be able to do the following:

• Add, modify, and remove tables
• Add, modify, drop, and execute stored procedures
• Add, modify, and remove views
• View, insert, and drop records in these tables.
• Be the owner of the database

Additionally, this account needs to also do the following:

• view information from the msdb database
• execute some system stored procedures.

The Notification Server user account will need “dbcreator” permissions in order for the Notification Server installation process to create the Symantec_CMDB Database. If the SQL administrator creates the Symantec_CMDB Database prior to the Notification Server installation, then this permission is not necessary; just point it to the installation process of the already present Symantec_CMDB Database.

On the Symantec_CMDB Database itself, the user will need the following permissions:

• Public - General access to the database.
• db_ddladmin - Adds, modifies, or drops objects in the database.
• db_datareader - Sees all data from all user tables in the database.
• db_datawriter - Adds, changes, or deletes data from all user tables in the database.
• dbo_owner - Owns the database.
  
  NOTE: Required server roles:
  sysadmin, diskadmin, serveradmin
  
  NOTE: Preferred server permissions:
  CREATE DATABASE, CREATE ANY DATABASE, ALTER ANY DATABASE

Additionally, when the Symantec Installation Manager (SIM) is installing SMP, if the Altiris database does not already exist, then this account needs to be able to create the Altiris database.

To verify that the Application Identity is DBO on the database, run the following SQL command as the Application Identity account:

• use Symantec_CMDB  --or replace with the name of the Notification Server database
• select user

If that returns anything other than "dbo" then the user is not the DBO of the database.