Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 5 down vote favorite

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?

share|improve this question

2 Answers

up vote 5 down vote accepted

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

share|improve this answer
I can put a TRY CATCH on but I can't see what the error is as I don't appear to be able to write to my log-table! I have quite limited access to the server itself. – El Ronnoco Sep 13 '12 at 10:53
Can you use RAISERROR then to throw it back out? You can use a SQL Agent alert to trap this RAISERROR. msdn.microsoft.com/en-us/library/ms180982.aspx – gbn Sep 13 '12 at 10:54
I don't want the proc to actually fail as the remote app is a live system – El Ronnoco Sep 13 '12 at 10:55
Use SQL profiler then to trap the error (Event = Errors and Warnings.. User Error message) – gbn Sep 13 '12 at 10:57
Good suggestion but I don't think I can open profiler :( Can you think of any reason why connecting from Java or .Net would make any difference - bearing in mind that both connections actually work and can hit the proc? – El Ronnoco Sep 13 '12 at 11:03
up vote 2 down vote

According to Microsoft (and you'd hope they'd know), as long as you've granted permissions to the SP then the underlying permissions will be sorted via permission chaining - http://msdn.microsoft.com/en-us/library/bb669058.aspx

Should mean that while your users can read from any table in the DB, they'd only be able to write to them via stored procs they'd got exec on.

share|improve this answer
Hmm yeah that's what I thought. So I can't figure out why there seem to be these failed INSERTs going on... – El Ronnoco Sep 13 '12 at 10:52
As gbn mentions, try/catch or profiler are the way to go. But how is the current logging set up? Can you pin point which IDs are missing, and is there anything 'special' about those? (ie, are you missing all the queries from 1 particular app server?) – Stuart Moore Sep 13 '12 at 10:56
I'm kind of working in the blind. I have an ID IDENTITY(1,1) on the log table and when I insert a row the ID will have jumped - but I can't tell why. I can only assume it is a failed INSERT from when the remote app has executed the proc. – El Ronnoco Sep 13 '12 at 11:06
So you're adding an entry before, and an entry after the body of the SP has completed. Is is possible to tell if all the missing ones are 'completeds'? Could be that some parameter is causing the SP to bail out and not process the final insert. If all connections are via the same login, and all access is via that SP, then the fact that you're getting some writes means that the permissions are fine. Now it's working out why only some inserts aren't making it. – Stuart Moore Sep 13 '12 at 11:17
Yes I'm writing before and after. So I insert a new row at the top and then update that row at the end (with the time). Only my writes are working, even when I connect with the logon the remote app is using. All other writes are apparently failing. I think I need profiler! – El Ronnoco Sep 13 '12 at 11:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.