SQL rights are needed for the Application Identity, service account, or SQL account - IT Management Suite

Products

• IT Management Suite
• Client Management Suite

Issue/Introduction

What SQL Server permissions or database roles does the Application Identity (App ID), service account, or SQL account need to work with Notification Server or Symantec Management Platform?
This article lists the required server-level and database-level permissions, and describes where to view, assign, and verify them in SQL Server Management Studio (SSMS).

Environment

ITMS 8.7.x,8.8.x

Resolution

NOTE: The following content applies to Symantec Management Platform (SMP Server or Notification Server (NS)) versions 8.7.x and later.

Minimum SQL security requirements

The account that SMP 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
• Own the database

This account also needs to:

• View information from the msdb database
• Execute certain system stored procedures

The SMP Server user account needs dbcreator permissions so the SMP Server installation process can create the Symantec_CMDB database. If the SQL administrator creates the Symantec_CMDB database before Notification Server installation, this permission isn't necessary — point the installer to the existing database instead.

On the Symantec_CMDB database itself, the account needs the following database roles:

• public — general access to the database
• db_ddladmin — adds, modifies, or drops objects in the database
• db_datareader — reads all data from all user tables in the database
• db_datawriter — adds, changes, or deletes data from all user tables in the database
• db_owner — owns the database

NOTE: Preferred server-level permissions: CREATE DATABASE, CREATE ANY DATABASE, ALTER ANY DATABASE

If Symantec Installation Manager (SIM) is installing SMP Server and the Symantec_CMDB database doesn't already exist, this account also needs permission to create it.

Where to view and assign these permissions in SQL Server Management Studio (SSMS)

1. Open SQL Server Management Studio and connect to the SQL Server instance that hosts the Symantec_CMDB database.
2. In Object Explorer, expand the server node, then expand Security > Logins.
3. Right-click the login used as the Application Identity, service account, or SQL account, then select Properties.
4. In the Login Properties window, select the Server Roles page. Assign server-level roles here, including dbcreator if the account needs to create the database, or the preferred permissions noted above if using those instead.
5. In the Login Properties window, select the User Mapping page.
6. Under "Users mapped to this login," check the box next to Symantec_CMDB.
7. In the "Database role membership" pane below, select: public, db_ddladmin, db_datareader, db_datawriter, and db_owner.
8. Click OK to apply the changes.

Verify the account is DBO on the database

To confirm the Application Identity is the database owner (DBO):

1. Open a new query window in SSMS, connected as the Application Identity account.
2. Run:

   use Symantec_CMDB
   select user

3. If the result is anything other than dbo, the account is not the DBO of the database. Return to the User Mapping page in Login Properties and confirm db_owner is selected under database role membership.

Additional Information

Installing Symantec Management Platform and IT Management Suite