FAQ on the Settings the EPM SQL databases use

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Endpoint Privilege Manager (EPM) Articles
7 min read
L2 Linker
No ratings

Question

Note: Many DBA’s review our database setting and have questions on why and what these settings are used for.  Here are the most common issues

Q1:
Does Viewfinity user need to be assigned the sysadmin role?

Q2:
Currently Viewfinity needs to have Trustworthy setting enabled. Why?

Q3:
Why do we need xp_cmdshell enabled in SQL?

Q4:
What Permission the db_backupoperator have and what it needed for? 

Answer

A1:
SA is required during upgrade only, it's possible to remove it afterwards. For the regular work we require DBOwner only.
Some examples of the actions performed by our installation that require the "SA" Role are as follows:
 
  • Add TRUSTWORTHY property (for unsafe/external assemblies - see below)
  • Enable CLR (to allows .NET code to be executed in scope of SQL DB)
  • Add unsafe assemblies (see below)
  • Detect backup Folder in Windows Registry (to configure the Viewfinity DB backup)
  • Various actions that overall require combinations of various different Roles, that is achieved in the simplest way by using the "SA" Role, e.g.:
    • Temp DB creation during upgrade / DB creation during installation (requires "dbcreator" Role)
    • Backup of the Viewfinity database: to check/create backup directory on disk; to delete old backup files,etc. (requires "diskadmin" Role)

A2:
The TRUSTWORTHY property is required for UNSAFE assemblies (see below). The assemblies should have UNSAFE_ACCESS permission due to the following reasons:
  • The assemblies are actively used from within the DB Stored Procedures and Functions. If an assembly does not have UNSAFE_ACCESS permission, it is unloaded after each execution of a call that causes dramatic performance degradation (up to 1000%), that sometimes makes the procedures just unusable.
  • The assemblies that do not have UNSAFE_ACCESS permission has no access to some built-in .NET framework functionality

A3:
The value xp_cmdshell is set to 'TRUE', to enable free-disk space check, by the upgrade backup process. It is needed only for installation/upgrade, and it can be removed later.


A4:
db_backupoperator Permission is to perform backup of EPM DB from within the EPM Admin Console.  It is needed only for installation/upgrade, and it can be removed later.

Issue

Note: Many DBA’s review our database setting and have questions on why and what these settings are used for.  Here are the most common issues
Q:
Does Viewfinity user need to be assigned the sysadmin role?
A:
SA is required during upgrade only, it's possible to remove it afterwards. For the regular work we require DBOwner only.
Some examples of the actions performed by our installation that require the "SA" Role are as follows:
 
  • Add TRUSTWORTHY property (for unsafe/external assemblies - see below)
  • Enable CLR (to allows .NET code to be executed in scope of SQL DB)
  • Add unsafe assemblies (see below)
  • Detect backup Folder in Windows Registry (to configure the Viewfinity DB backup)
  • Various actions that overall require combinations of various different Roles, that is achieved in the simplest way by using the "SA" Role, e.g.:
    • Temp DB creation during upgrade / DB creation during installation (requires "dbcreator" Role)
    • Backup of the Viewfinity database: to check/create backup directory on disk; to delete old backup files,etc. (requires "diskadmin" Role)
Q:
Currently Viewfinity needs to have Trustworthy setting enabled. Why?
A:

The TRUSTWORTHY property is required for UNSAFE assemblies (see below). The assemblies should have UNSAFE_ACCESS permission due to the following reasons:
 
  • The assemblies are actively used from within the DB Stored Procedures and Functions. If an assembly does not have UNSAFE_ACCESS permission, it is unloaded after each execution of a call that causes dramatic performance degradation (up to 1000%), that sometimes makes the procedures just unusable.
  • The assemblies that do not have UNSAFE_ACCESS permission has no access to some built-in .NET framework functionality
Q:
Why do we need xp_cmdshell enabled in SQL?
A:

The value xp_cmdshell is set to 'TRUE', to enable free-disk space check, by the upgrade backup process.


Q:

What Permission the db_backupoperator have and what it needed for? 

A:
db_backupoperator Permission is to perform backup of EPM DB from within the EPM Admin Console.

Steps

Note: Many DBA’s review our database setting and have questions on why and what these settings are used for.  Here are the most common issues
Q:
Does Viewfinity user need to be assigned the sysadmin role?
A:
SA is required during upgrade only, it's possible to remove it afterwards. For the regular work we require DBOwner only.
Some examples of the actions performed by our installation that require the "SA" Role are as follows:
 
  • Add TRUSTWORTHY property (for unsafe/external assemblies - see below)
  • Enable CLR (to allows .NET code to be executed in scope of SQL DB)
  • Add unsafe assemblies (see below)
  • Detect backup Folder in Windows Registry (to configure the Viewfinity DB backup)
  • Various actions that overall require combinations of various different Roles, that is achieved in the simplest way by using the "SA" Role, e.g.:
    • Temp DB creation during upgrade / DB creation during installation (requires "dbcreator" Role)
    • Backup of the Viewfinity database: to check/create backup directory on disk; to delete old backup files,etc. (requires "diskadmin" Role)
Q:
Currently Viewfinity needs to have Trustworthy setting enabled. Why?
A:

The TRUSTWORTHY property is required for UNSAFE assemblies (see below). The assemblies should have UNSAFE_ACCESS permission due to the following reasons:
 
  • The assemblies are actively used from within the DB Stored Procedures and Functions. If an assembly does not have UNSAFE_ACCESS permission, it is unloaded after each execution of a call that causes dramatic performance degradation (up to 1000%), that sometimes makes the procedures just unusable.
  • The assemblies that do not have UNSAFE_ACCESS permission has no access to some built-in .NET framework functionality
Q:
Why do we need xp_cmdshell enabled in SQL?
A:

The value xp_cmdshell is set to 'TRUE', to enable free-disk space check, by the upgrade backup process.


Q:

What Permission the db_backupoperator have and what it needed for? 

A:
db_backupoperator Permission is to perform backup of EPM DB from within the EPM Admin Console.

Note: Many DBA’s review our database setting and have questions on why and what these settings are used for.  Here are the most common issues

Q:

Does Viewfinity user need to be assigned the sysadmin role?

A:

SA is required during upgrade only, it's possible to remove it afterwards. For the regular work we require DBOwner only.

Some examples of the actions performed by our installation that require the "SA" Role are as follows:

 

  • Add TRUSTWORTHY property (for unsafe/external assemblies - see below)
  • Enable CLR (to allows .NET code to be executed in scope of SQL DB)
  • Add unsafe assemblies (see below)
  • Detect backup Folder in Windows Registry (to configure the Viewfinity DB backup)
  • Various actions that overall require combinations of various different Roles, that is achieved in the simplest way by using the "SA" Role, e.g.:
    • Temp DB creation during upgrade / DB creation during installation (requires "dbcreator" Role)
    • Backup of the Viewfinity database: to check/create backup directory on disk; to delete old backup files,etc. (requires "diskadmin" Role)

Q:

Currently Viewfinity needs to have Trustworthy setting enabled. Why?

A:

The TRUSTWORTHY property is required for UNSAFE assemblies (see below). The assemblies should have UNSAFE_ACCESS permission due to the following reasons:

 

  • The assemblies are actively used from within the DB Stored Procedures and Functions. If an assembly does not have UNSAFE_ACCESS permission, it is unloaded after each execution of a call that causes dramatic performance degradation (up to 1000%), that sometimes makes the procedures just unusable.
  • The assemblies that do not have UNSAFE_ACCESS permission has no access to some built-in .NET framework functionality

Q:

Why do we need xp_cmdshell enabled in SQL?

A:

The value xp_cmdshell is set to 'TRUE', to enable free-disk space check, by the upgrade backup process.

 

Q:

What Permission the db_backupoperator have and what it needed for? 

A:

db_backupoperator Permission is to perform backup of EPM DB from within the EPM Admin Console.

 

 

 

Rate this article:
  • 1865 Views
  • 0 comments
  • 0 Likes
Article Dashboard
Version history
Last Updated:
‎08-04-2020 09:50 AM
Updated by: