EEE SQL Query Executor

As of EEE version 1.84.09 a new SQL Query Executor function is available within EEE.

This screen allows users to run ad-hoc T-SQL queries against the EEE database using an appropriate SQL authentication credential.

Although EEE issue warnings if the query text contains specific keywords, EEE does not analyse the syntax of the query nor attempt to prevent execution of destructible queries.

As such, the following security considerations are recommended:

• Disable the screen for all but specific users who have knowledge of creating and running T-SQL queries. This can be done via user privileges.
• Set up the SQL authentication login with the minimum permissions required (see below).
• Use a complex password for the SQL login in order to improve security.
• Create separate SQL logins for different EEE databases to further compartmentalise access.

SQL Login Permissions

How to restrict access to the SQL login via permissions

Deny visibility of other databases

Initially run the following to hide other database names to logins using the public server role.

USE MASTER
GO
DENY VIEW ANY DATABASE TO PUBLIC
GO

Server roles

Never grant the sysadmin server role to an SQL login intended for use with the SQL Query Executor page. This would yield unrestricted access on the SQL server to the login.

Only the default public server role is recommended.

User Mapping

The SQL login must only be mapped to as a user on the appropriate EEE database. This will restrict the user from accessing other databases. e.g.

Database role membership

We recommend only granting the minimum permissions required for the login:

• db_datareader - Grant membership to this role to allow reading data from all non-system tables in the database
• db_datawriter - Only if required. Grant membership to this role to allow insert/update/delete of data from all non-system tables in the database

The above roles affect all non-system tables in the database. In order to only allow specific permissions to specific tables, you'll need to manually choose to grant X permission to Y table. e.g.

USE EEE
GO
GRANT SELECT on Product to sqlqueryexecutor
GO

As of EEE version 1.84.09 a new SQL Query Executor function is available within EEE.

This screen allows users to run ad-hoc T-SQL queries against the EEE database using an appropriate SQL authentication credential.

Although EEE issue warnings if the query text contains specific keywords, EEE does not analyse the syntax of the query nor attempt to prevent execution of destructible queries.

As such, the following security considerations are recommended:

• Disable the screen for all but specific users who have knowledge of creating and running T-SQL queries. This can be done via user privileges.
• Set up the SQL authentication login with the minimum permissions required (see below).
• Use a complex password for the SQL login in order to improve security.
• Create separate SQL logins for different EEE databases to further compartmentalise access.

SQL Login Permissions

How to restrict access to the SQL login via permissions

Deny visibility of other databases

Initially run the following to hide other database names to logins using the public server role.

USE MASTER
GO
DENY VIEW ANY DATABASE TO PUBLIC
GO

Server roles

Never grant the sysadmin server role to an SQL login intended for use with the SQL Query Executor page. This would yield unrestricted access on the SQL server to the login.

Only the default public server role is recommended.

User Mapping

The SQL login must only be mapped to as a user on the appropriate EEE database. This will restrict the user from accessing other databases. e.g.

Database role membership

We recommend only granting the minimum permissions required for the login:

• db_datareader - Grant membership to this role to allow reading data from all non-system tables in the database
• db_datawriter - Only if required. Grant membership to this role to allow insert/update/delete of data from all non-system tables in the database

The above roles affect all non-system tables in the database. In order to only allow specific permissions to specific tables, you'll need to manually choose to grant X permission to Y table. e.g.

USE EEE
GO
GRANT SELECT on Product to sqlqueryexecutor
GO

Executing Stored procedures

Use only if required

In order to only allow executions of specific stored procedures, you'll need to manually grant the execute permission. e.g.

USE EEE
GO
GRANT EXECUTE on usp_Article_Get to sqlqueryexecutor
GO

Warning: Execute permissions are lost if the the stored procedure is deleted and re-created.

For permissions to execute all stored procedures, create a new role for the dbo schema and grant execute permissions there.

USE EEE
GO
CREATE ROLE sqlqueryexecutor_dbo
GO
GRANT EXECUTE ON SCHEMA::dbo to role_exec_dbo
GO
ALTER ROLE sqlqueryexecutor_dbo ADD MEMBER sqlqueryexecutor

Warning: Use this permission with extreme caution. Incorrect usage of EEE stored procedures can have unintended consequences and may cause irreparable damage or loss of data

Database Mail

To allow the SQL login to send emails via SQL Server Database Mail, create and map the login to a user on the msdb database, and grant the DatabaseMailUserRole role.

USE msdb
GO
CREATE USER sqlqueryexecutor FOR LOGIN sqlqueryexecutor
GO
USE msdb
GO
ALTER ROLE DatabaseMailUserRole ADD MEMBER sqlqueryexecutor
GO