What are the required permissions when using a SQL database with legacy Marshal products?

This article applies to:

• Microsoft SQL and SQL Express
• Trustwave MailMarshal (SEG) 6.7 and below
• MailMarshal Exchange 5.X

Question:

• What are the required permission levels when using a SQL database with MailMarshal or WebMarshal?

Scope of this article:

• For new database permission functionality in MailMarshal SMTP 6.8 and above, MailMarshal Exchange 7.0 and above, please see Trustwave Knowledgebase article Q12939.

Information:

The Microsoft SQL security model uses the concepts of "logins" and "roles."

• Logins can be Windows accounts or SQL logins.
  • Note that earlier versions of Marshal products require you to use a SQL login to connect. WebMarshal 6.x allows you to use either Windows or SQL logins.
• Logins can be granted or denied access to each database.
• Roles define the actions that each login can perform, either at the server level or for a specific database.
• You can create logins, grant database access, and assign database roles from the SQL Server Enterprise Manager or SQL Server Management Studio.
  • Expand [server name] | security | logins, and view the properties of the login.
  • Use the Database Access tab or User Mapping item on the properties window to grant database access and permissions. For detailed help, see SQL Server documentation.

Levels of access required

For requirements affecting current supported product versions, see Trustwave Knowledgebase article Q12939.

• If you create a new login for use with Marshal databases, ensure that it has access to the Master database and the Marshal databases.
• System Administrator server role is required when creating, re-creating, upgrading, changing or moving a database. Normally, you would use the default SA (System Administrator) logon.
  • These operations use multiple databases and they create and drop numerous SQL objects.
  • If you use another login to access the Marshal product database in daily operation, you must change the login used before you run the product upgrade installer.
• DBO (Database Owner, db_owner) role for the database is the minimum practical permission level to perform the day to day functions of using the Marshal product database. This includes using stored procedures and changing database table entries.
  • If you use another login to access the Marshal product database in daily operation, grant it this role for the database.
  • Advanced SQL Server administrators may be able to grant the required permissions on tables and stored procedures without using the DBO role, but this is a complex task and it would require review after each product upgrade, when new objects may have been created.
• [Public] access to a database can be used when only access to running reports is required. The account must have permission to execute all stored procedures and read the data.