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. I
t 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. I
t 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 issuesQ: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.