Required SQL Server login permissions

The Tealeaf application requires three SQL Server logins to operate correctly.

1. During installation, a SQL Server login is required with sufficient permissions to create and modify the Tealeaf databases. This login is used only during installation.
2. An Administrative login enables background processes to modify the database schema during normal operation.
3. A User login is used by all customer-facing portions of the application and only permits read and write access to the databases.

Installation login

The SQL Server login that is used during installation needs the following permissions to install and modify the schemas of each Tealeaf database.

This login can either be the NT account of the user running the installation or a separate SQL Server login that is provided to the Database Manager as a secondary step after the main installation completes.

The simplest option is to use an NT Account that has SQL Server admin privileges or provide a SQL login with that level of access. If that is not possible a login with the following roles and permissions is required to successfully complete the Tealeaf database installation:

• Required Database Roles:
  • db_ddladmin
  • db_datareader
  • db_datawriter
• Required Database Permissions:
  • ALTER ANY LOGIN
  • ALTER ANY USER
  • CREATE DATABASE
  • CREATE SCHEMA

Administrative login

This login is used by Tealeaf applications that must be able to modify the schemas of one or more of the Tealeaf databases. The applications that use this account that is run in the background and do not allow user interaction.

• Default Account Name - TLADMIN
• Required Database Roles:
  • db_ddladmin
  • db_datareader
  • db_datawriter
• Optional Database Permissions: VIEW SERVER STATE is useful for performance monitoring.

Additional admin login permissions

Through the Portal, database administrators can access useful reports on the state of the Tealeaf databases.

To acquire all of the information to populate the Database Filegroup Size report, the TLADMIN account requires extra permissions. Below are the permissions to enable:

USE MASTER
GO

EXEC sp_grantdbaccess 'TLADMIN';

GRANT EXECUTE ON sys.sp_OACreate TO TLADMIN;
GRANT EXECUTE ON sys.sp_OADestroy TO TLADMIN;
GRANT EXECUTE ON sys.sp_OAGetErrorInfo TO TLADMIN;
GRANT EXECUTE ON sys.sp_OAGetProperty TO TLADMIN; 

If these permissions are not enabled or are not possible, the Database Filegroup Size contains a significant number of zeros in the data.

User login

This login is used by the Tealeaf Portal and any other Tealeaf applications that require database access and allow user interaction. The login is limited to reading and writing data from the Tealeaf databases.

• Default Account Name - TLUSER
• Required Database Roles:
  • db_datareader
  • db_datawriter

Database security

Follow these suggestions when you set up database security for Tealeaf:

• You can set the default database for the Tealeaf login properties, TLADMIN and TLUSER, to any valid value.
• No stored procedures in any Tealeaf databases should have access that is granted to public. All access should all be limited explicitly to TLADMIN and TLUSER. Revoking permissions from public does not cause an issue.
• Many system stored procedures in TL_SYSTEM database have run privileges that are granted to public. Revoking these permissions should not cause problems. However, since revoking these permissions requires changes to the MS SQL Server system procedures, Tealeaf cannot guarantee that the changes does not result in system issues. The Tealeaf SQL Server logins are explicitly granted the permissions that they need to access any system-stored procedures.