Database User Does Not Have Required Permissions

Error Summary

• Hevo was unable to connect to the database or schema in the configured SQL Server instance.

Potential Causes

• The database user details are incorrect.

• The user authorizing the connection does not have sufficient permissions to access the database.

Suggested Action(s)

• Verify that the database user details are correct.

• Verify that the database user configured in Hevo has the SELECT privilege to connect and read data from your SQL Server database. To confirm that the database user has the required permissions, connect to your SQL Server database as a masteruser with any SQL client tool, such as sqlcmd, and run the following script:

  EXECUTE  AS  USER  =  '<database_username>';  -- Impersonate the Hevo user 
  
  SELECT HAS_PERMS_BY_NAME('<database_name>',  'DATABASE',  'SELECT')  AS DatabaseHasSelectPermission, 
         HAS_PERMS_BY_NAME('<schema_name>',  'SCHEMA',  'SELECT')  AS SchemaHasSelectPermission; 
  
  REVERT;  -- Revert to your original login
  

  If the result returns 0 for any permission, it means that the user does not have that privilege. To grant the missing privileges, run the following script:

  -- Grant SELECT privilege at the database level 
  GRANT  SELECT  ON  DATABASE::<database_name>  TO  <database_username>; 
  -- Grant SELECT privilege at the schema level 
  GRANT  SELECT  ON  SCHEMA::<schema_name>  TO  <database_username>;
  

  Note: Replace the placeholder values in the commands above with your own. For example, replace <database_username> with hevo_user.