Hello all I am working with SQL 2012 and trying to get a secure link from SSRS to stored procedures which in turn access tables. Basically I have SSRS connecting to SQL via a SQL user (say SSRSToSQL). This user has rights to execute relevant stored procedures ONLY. Inside the stored procedure is an "execute as login = 'SQLReadOnly' which is an account with read only access to most databases.
Using management studio I log in as SSRSToSQL. I can then successfully run the following lines but only one by one. If I highlight all three and run them I get "Login failed for user 'SSRSToSQL'"
execute as LOGIN='SQLReadOnly'; select top 100 * from MyLinkedServer.MyDatabase.dbo.MyTable; revert
If I put those three lines into a stored procedure and execute it as SSRSToSQL it fails stating, quite rightly, that SSRSToSQL can't access MyTable. It is like it is ignoring the "execute as" statement as it did with the three lines above when run together.
Is there something which checks the securities before running? If so it could be jumping to the wrong conclusion that the SP won't run under current auths rather than seeing that the data will be retrieved under a different login.
There is a forehead shaped dent in my desk over this one so any help gratefully received!
Yes that is the way I am trying to work it but it still fails referring to the "external" login rather than the "execute as" login. It just seems to evaluate the contents of the stored procedure against the curtrent login rather than the one I am telling it to use inside the SP and hence it fails. Seriously frustrating!