Configuring EFT Server Permissions in SQL Server


THE INFORMATION IN THIS ARTICLE APPLIES TO:

  • EFT Server v6.0 and later

DISCUSSION

When you install EFT Server with the Auditing and Reporting Module and a SQL database, the installer runs scripts to connect EFT Server to SQL Server. The EFT Server service must have read, write, and execute permissions in SQL Server. For example, if SQL Server goes offline, EFT Server will continue to audit activity using its flat files, then when SQL Server comes back online, EFT Server will connect to SQL Server and upload its flat file contents to SQL Server. If EFT Server does not have the correct permissions on SQL Server, the upload will fail.

During installation, EFT Server needs full DB Owner access to the auditing database to set up the schema. During updates or upgrades, EFT Server needs full DB Owner access to update the schema. Once it is set up, EFT Server only needs to be able to read, write, and execute stored procedures.

  • In SQL 2008 Express, if you want to change the login that the EFT Server service is running as, then you have to install Management Studio Express and add that as a login for EFTDB.
  • In SQL 2008 R2, you must grant Execute permission to the EFT Server service accountname (e.g., demo\svc_eft).
  • In SQL 2008 R2, you can verify permissions in the Database Properties dialog box:

To add a user in SQL Express 2008

At a command prompt, type the following commands. In this example, "WIN-P0\testuser” is the user. On the local computer, you can drop the system name and just use the username (e.g., “testuser”).

osql -E -S localhost\GLOBALSCAPE -Q "sp_grantlogin 'WIN-P0\testuser'"

osql -E -S localhost\GLOBALSCAPE -Q "sp_addsrvrolemember @loginame='WIN-P0\testuser', @rolename='sysadmin'"