I have a stored procedure which is accessed remotely by an app which is running on some kind of Java/WebSphere type platform. It takes one argument, returns some data and that's that.
Recently there has been some talk of it maybe running slowly - I'm pretty confident that there is no reason for the SP to be running slowly. But I added a log table to the DB and made the SP write to it on entry and again after the
SELECT is complete so I can see how long the calls are taking.
This change works fine for my Windows Authentication log-in - and it also works fine when I use the application log-in which the remote app is using. However I am doing the connection from the .Net environment and not from Java.
When I changed the SP to do the logging I noticed that the IDs I was
INSERTing into the log-table were jumping - indicating that failed INSERTs had been taking place - presumably caused by the remote-app (as that is the only thing that uses this SP).
The application log-on has only
db_datareader role assigned to it. I have
EXEC to the application log-on of course.
Is it the case that the SP will not be able to
INSERT a row because the accessing log-on has only
db_datareader? And if so, how come the SP does the
INSERT when I use that same log-on?