I am new to Service Broker but I'm trying to use it to run a stored procedure as a background process as it may take many hours to complete.
I used the example provided by Remus Rusanu at http://rusanu.com/2009/08/05/asynchronous-procedure-execution/, which is excellent and kicks off a stored proc just fine.
The stored proc runs fine when called from SSMS but fails when I try to run it under SB. I can run other, simple sprocs using SB, so that part is working. What appears to be the problem is that SB is running in a different security mode than my user, which has the necessary permissions (ie BULKADMIN).
I am no SQL Server guru, so I don't understand why, when CURRENT_USER is dbo, when I do "execute as user='dbo'" and check "is_srvrolemember ('bulkadmin')" it returns false. But I guess this is something to do with my problem.
Is the problem solveable, or do I have to work out another way of getting background imports to run?
Hope you can help,