First of all, thank you to those who tried to help.
I managed to get it working.
What I needed to do:
- Give the user the webapplication uses to connect to the database access to the MSDB database via the user-mapping in sql management studio.
- Set the role membership for this user and database: I checked SQLAgentOperatorRole, SQLAgentReaderRole and SQLAgentUserRole.
- Created a JOB in SQL Agent that runs the script that moves the data in a transaction. No scheduling settings. Job owner is the user the application uses.
- Created a Stored Procedure that runs the msdb.dbo.sp_start_job procedure
- Call from my code this stored procedure.
- My webapplication checkes every second, by javascript timer, if the transaction is still running.
Why it went wrong.... ME!
One of my mistakes was executing the SP with owner = ''. removing that fixed it.
It is now running! yay!