Required SQL Server Permissions

Before You Start

• When running the Spirion console installer to install or upgrade, the user needs to have sa privileges.
• After the installation is complete, the required permission to connect to the database or open the CAT (Console Administrator Tool) is dbo. 
• The installer must create the database. This linked article explains the validations performed when installing:
• • https://support.spirion.com/hc/en-us/articles/115000019051
• Post install, dbo permissions are required for a user to open the CAT and the SQL user that may be configured in the CAT to connect to the database is basically that of db_owner
• If the database is created and then moved to another SQL Server you would then need to run the installer on the restored database so that would still require the elevated privileges needed during an install or upgrade.
• • You can give specific permissions rather than give sa or dbo permissions.
  • If permission is missing the installer will throw an error and when clicking on the details of the error in the installer you will be presented with the missing permission.

Permissions for Installing and Upgrading

Below is a list of the permissions needed for installing and operations after installing:

• CREATE DATABASE permission required in the 'master' database to create a new database (required for new installations only)
• CREATE ANY DATABASE permission required to create a new database (required for new installations only)
• ALTER ANY CONNECTION permission required to use the KILL command when upgrading the database (optional for upgrades only - when missing, the installer will not try to kill existing connections. In most cases the database upgrade will succeed, as the current version of Microsoft Tools used to perform database changes should be able to work around requiring this permission.)
• ALTER DATABASE permission is required to configure the database when upgrading (required for upgrades only)
• ALTER ANY DATABASE SCOPED CONFIGURATION permission
• • To grant this permission to a user or role, you would use the GRANT statement:

  GRANT ALTER ANY DATABASE SCOPED CONFIGURATION TO [username_or_rolename];

• ALTER ANY LOGIN privileges are required to install or upgrade the database (optional for upgrades only - when missing, the installer will not try to create a db user if one is missing.
• • This can cause failure if the NT AUTHORITY\NETWORK SERVICE account requires permissions and is not properly granted access to the database.)
• Execute sp_fulltext_database stored procedure permission is required to install or upgrade the database (optional for new installations or upgrade and only required when the Full-Text Search service is installed.)
• ALTER permissions on the upgrade database are required
• ALTER ANY USER privileges are required to upgrade the database (optional for upgrades only - when missing, the installer will not try to create a db user if one is missing. This can cause failure if the NT AUTHORITY\NETWORK SERVICE account requires permissions and is not properly granted access to the database.)
• User must be able to create tables in the tempdb databases
• User must be able to select data from tables in tempdb databases

User Permissions Required for Daily Use

The user logged into the server and opening the CAT (Console Administrator Tool) needs admin rights to the server and dbo permissions to the database.

• The user who ran the installer needs to retain admin rights to the server. 
• When you run the installer using Windows authentication for the SQL Server it uses the Network service to communicate with SQL Server.
• The account that ran the installer still requires:
• • admin rights for the Console’s server
  • dbo permissions to the database after the installation/upgrade is complete

FAQs

• The middle tier connects with 2 IDs.  
• Which user requires permissions?
• • The user that runs the Spirion Server service
  • The user that runs Spirion web services and Console
  • Both?
• The Spirion Server service requires the permission dbowner on the Spirion database only
• • The remaining permissions are needed for the Web Service / Console
• If the user already enjoys the db_owner permission on the Spirion database, why does the user need the ALTER ANY DATABASE privilege for regular operations?
• • "ALTER DATABASE" OR "ALTER ANY DATABASE". 
  • ALTER DATABASE on the Spirion database is all that is required.
• To run the requested diagnostics on Monday, does the user need only regular permissions or the install level permissions?
• • To run the Gather Data function, only regular permissions are needed.
• Spirion recommends the console service account have the permission db_owner

Query Notification

Spirion uses SQL Server's built-in Query Notification functionality, which relies on Service Broker.  

• ALTER DATABASE permissions enable Spirion to perform certain Service Broker actions, if needed.
• Gather Data is executed using the account which is configured within the CAT tool on the Database Settings tab if set to use SQL authentication.  
• If set to use Windows Authentication, the process is executed with the credentials of the logged-in user.

SQL server read and write intensity

Is SQL more read-intensive or write-intensive?

The Console database is more read-intensive based on several different installs.