Will a SP which writes records fail if accessed by a db_datareader role?

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 GRANTed 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?

If the same user owns both table and stored procedure (usually dbo), then permissions are not checked. This is known as "ownership chaining".

So you could have a DENY on the table and it won't be checked. db_datareader makes no difference: it isn't checked.

If the owners are different (and note that it's the owner of the schema that matters) then rights will be needed. See the MSDN example above.

Anyway, you need to catch the errors to make sure, either by TRY/CATCH or via SQL Profiler

StackExchange Database Administrators Q#24249, answer score: 5