1

I would like to read a view within a SSIS package. Yet the package fails with the message: "Syntax error, permission violation, or other nonspecific error".

The SSIS package is executed by a SSIS Package Execution proxy with the intend to limit the permissions on the account set as a credential to only those required.

If I grant the account sysadmin on the target instance the SSIS package works. If I grant the account only the rights to access the database in question, the SSIS package fails. (Even if I assign it tp the "db owner" role) I did grant the account rights to the referenced database, yet it failed.

Is there an option to run the SSIS package using the proxy and avoid granting the credential account sysadmin rights?

The SSIS package is placed upon a SQL Server 13.0.4001.0 instance.

The databases are placed upon a SQL Server 10.50.2796.0 instance.

The account in question is an Active Directory account.

Jennifer Fuss
  • 43
  • 10
  • >>>Syntax error, permission violation, or other nonspecific error<<< This is not SQL Server error message, why are you sure it's the permissions issue? – sepupic Oct 10 '17 at 12:27
  • To narrow down the error launch the Profiler and see what query and under which ligin is executed. If you see nothing, the execution on the server was not even started – sepupic Oct 10 '17 at 12:29
  • ...And here is the solution (it's NOT permission issue): https://blogs.msdn.microsoft.com/dataaccesstechnologies/2012/08/20/syntax-error-permission-violation-or-other-nonspecific-error/ – sepupic Oct 10 '17 at 12:31
  • I thought it could be a permission issue as increasing the permission on the executing account to sa worked. I will check your solution. Thank you. – Jennifer Fuss Oct 10 '17 at 13:15
  • I did try the solution by adapting the SSIS package. Yet it had no effect. The access rights to the view still aren't provided unless I run the SSIS package with an account that's in the sa role. – Jennifer Fuss Oct 11 '17 at 15:24
  • Why don't you use Profiler to see under which account it is executing? Or you can try to select user, original_login() from your package and see what user is used. At least you'll see if this is a permission issue and to which user should you give permissions – sepupic Oct 11 '17 at 15:30

0 Answers0