I've been playing around with trying to automate emailing a basic report. I've set up Database Mail, which is working well. I've written the basic report code, which runs fine when run directly as a query from management studio (generates & sends email and attaches the .csv file containing the correct info).
Problem is when the job runs, it creates the email and attaches a .csv but the .csv only contains the following error:
Msg 916, Level 14, State 1, Server DB-SERVER\TESTDB, Line 1
The server principal "DB-SERVER\Administrator" is not able to access the database "TESTDB" under the current security context.
I've done quite a bit of 'Googling', there is quite a lot of info on the error but I still cant work out how to sort it, any pointers or ideas?