Error when job runs: The server principal "DB-SERVER\Administrator" is not able to access the database

SSAS and SSRS
1

Afternoon all,

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?

Thanks

Dave

2

In the job step definition, there is a place to optionally specify the security context that the step will run under. It sounds like, implicitly or explicitly, your job step is running under DB-Server\Administrator context and that it does not have access to the database. If true, you can either explicitly change the security context for that step to be one that has access or grant DB-Server\Administrator access to the database.

3

Thanks for the reply, a good thought but unfortunatly didn't help. A colleugue noticed this morning that the service was running with the wrong privs. we reset it to Local System Account, all working as it should now.

Start > Run > Services.msc > 'SQL Server agent (TESTDB)' > Double Click > Log On > Local System Account.