problem running system proc

Question

problem running system proc

Rogman

Ten Centuries

Points: 1332
More actions
October 19, 2011 at 5:20 pm

#261754

Hello-
I have a proc that I created that calls the msdb..sp_help_jobhistory system stored procedure. The proc runs fine under my admin account, but this is used by an SSRS report so I created a SQL login for the database connection. The proc runs under this account but doesn't return any rows or return an error. I created a db_executor role in both my user database and msdb and greated execute to the role and added the sql login to the role, but still no luck. When I give the sql login admin rights, it runs correctly, so it;s obviously a permissions issue. I also gave the loginn datareader and datawriter permissions on both DBs to no avail.
Any ideas what I'm missing?
Thanks,
Roger

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 1

Roger,

from your description I can't really tell what's wrong, but there are a couple of easier ways I think to resolve this issue. First of all you could add your SQL Login to one of the SQL Server Agent fixed database roles in the msdb database like SQLAgentUserRole.

This would allow sp_help_jobhistory but of course also a couple of others which you will need. Remember that sp_help_jobhistory will call a couple of other stored procedures during execution. Disadvantage might be that your login can then also be used to view jobs.

If you don't want that you could use the EXECUTE AS option in your stored procedure, but that might have other security implications.

[font="Verdana"]Markus Bohse[/font]

Rogman Ten Centuries Points: 1332 More actions · Answer 2

I added the user to the SQLAgentUser and SQLAgentReader roles and now it works. I figured datareader, datawriter and execute on the database would work. Though not a perfect solution it will do for now. Thanks for the help!

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

I've run into similar problems and was loath to grant any privs to any login other than PUBLIC. The way I've solved the problem was very simple. I made sure that the stored proc was owned by "SA" and then, within the proc, used the "WITH EXECUTE AS OWNER" procedure option. Then you can grant anyone privs to just execute the proc and nothing else... they don't even need privs to the underlying tables in the proc.

If you have "Last Modified By" triggers to update "LastModifiedBy" columns in any related tables, you'll likely need to change whatever you're currently using to populate that column to ORIGINAL_LOGIN().

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)