November 6, 2013 at 1:13 pm
I have an SSRS report that executes a stored procedure that truncates a table and populates it from another database on the same server. I'm trying to use the with execute as option because the sql login used by the reports data source only has read rights. But, I'm seeing error:
"Msg 916, Level 14, State 1, Procedure sp_procedure, Line 33
The server principal "sqllogin" is not able to access the database "database" under the current security context."
The sql login has read, write and ddladmin rights to both databases, any ideas?
There is an exception to every rule, except this one...
November 6, 2013 at 1:30 pm
Break it into two stored procedures (you can have a single stored procedure call them both in succession, if necessary). The first procedure should only do the truncate with the "EXECUTE AS" option, while the second does the rebuild of the data with no "EXECUTE AS" option. EXECUTE AS only works for database level rights, and does not extend to server level rights (such as the ability to log into a separate database).
November 6, 2013 at 1:38 pm
Almost, but the select statement joins from 2 databases. I do appreciate the reply.
There is an exception to every rule, except this one...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply