Best way to expose msdb information

Question

Best way to expose msdb information

seaport

SSCrazy

Points: 2491
More actions
April 2, 2014 at 9:03 am

#305903

One client application needs to know the run time and run status of a specific job. What is the best way to present the information without granting any unnecessary permissions? I do not want to grant the end user any permission to the msdb tables/views. So far the option I get is to create a stored procedure inside msdb and grant execute of this sp to the user. Still, creating user-defined procedure inside msdb concerns me.
Any suggestions?
Thanks in advance.

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

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

Steve Jones - SSC Editor SSC Guru Points: 728164 More actions · Answer 1

I'd keep things in the db. Use a synonym or three part naming to do this and execute it in the database. Make sure you include error handling if the job or information doesn't exist for some reason.

You can also use the WITH EXECUTE clause to avoid permission elevation and limit the extra permissions to your proc only.

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

ScottPletcher SSC Guru Points: 100262 More actions · Answer 2

If possible, I'd just add code to that job to write its status to a table in the other db. That way you could avoid referencing msdb at all from that db.

If you can't do that, you could create a view in the user db that reads a view against msdb that is limited to only that job, and grant the user access to that view only. But I'm not absolutely certain there isn't some way that permission chaining wouldn't then possibly allow the user to see other jobs as well.

SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

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

ScottPletcher (4/2/2014)
If possible, I'd just add code to that job to write its status to a table in the other db. That way you could avoid referencing msdb at all from that db.

I try to keep MSDB fairly clean and low profile so that's what I do. It also helps a whole lot during SOC 2, ISO, SOX, SEC, and a million other audits.

--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)
Intro to Tally Tables and Functions

seaport SSCrazy Points: 2491 More actions · Answer 4

Thanks for all the replies.

My test showed that, creating a view or sp in the user db does not work for me. With this approach, I have to assign the end user permissions on the msdb and on that user-defined view/sp.

So what I will do is to let the job write its status to a small table in the user db. This way I can keep the msdb clean and do not need to worry about any permission issues.