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: 736172 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.

ScottPletcher SSC Guru Points: 101093 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004318 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)

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.