@RoyiNamir I've a couple of part-complete scripts that do some of that. They're based off sys.sql_expression_dependencies and sys.sql_dependencies. Nothing I could share, unfortunately.

Note that while the application will have a dependency on all those objects it will only need to be granted permission to execute SP_1. The other objects are implicit once that's GRANTed (with a little bit of "it depends" sprinkled over that statement, of course!)

@RoyiNamir One of the "it depends" issues that Michael is talking about (and the only one I can think of actually) is when dynamic SQL is involved anywhere in the chain of calls invoked by calling the SP_1. You'll need permissions for all the objects referenced explicitly in the dynamic script(s).

Morning

Morning

Morning

Morning

@RoyiNamir obligatory plug for github.com/drewfurgiuele/Test-SQLScripts . it's not plug-and-play by any means, and suffers from a lot of the same weaknesses as the tsql/dmv approach, but it's fun

Thank you all. BTW it is new to me that if I have permission for SP_1 then it also includes TR_1

not super clear wording there? but i'd say "probably not" w/o specifics

yup, i saw the chat transcript. same answer ;)

> "probably not, but do a repro to be sure"

as in "you probably do need the extra GRANT"

do a repro in a lab environment so you're not getting a false positive from legacy settings you forgot about / were unaware of in your shared dev environment

fwiw - i've definitely been bit before by triggers blocking inserts to a base table because the user tripping the trigger did not have perms on a downstream object

Morning

@RoyiNamir Shameless plug for my blog post - georgepalacios.com/41-how-to-find-sql-object-dependencies

That should do at least part of the job

Unless there are any cross-server dependencies.

Or Dynamic SQL as mentioned above

Morning

@George.Palacios dbfiddle.uk/… if you're having fun with throw :)

@PeterVandivier Interesting

i usually find myself going a bit mad with formatmessage()

I've never bothered in all honesty. I found the internal error numbers of SQL to be so detracted from front-end apps I've never found a real use for using them properly

I normally just throw custom messages formed in the procedure

iirc raiserror handles localisation nicely, even for user defined messages

Yeah that makes sense

That could be fun using just throw

You'd almost need to write an error wrapping procedure that could consume the error, figure out the appropriate error message, then throw it

usually too much faffing, but just a fun nugget to have in the toolbox

Barely anyone even does the basics of SQL error handling so this is like Super advanced compared to most stuff you see out there

IE BEGIN TRAN with no try/catch or error handling

i often prefer raiserror for the with log and nowait options

not so much for try...catch shenanigans

I suppose really they're for different purposes

Which should have been more obvious to me given that they haven't depracated raiserror

good morning captain george!

I got rid of my ship actually

Hey if I buy a boat can I legally change my salutation to Cpt

That'd be pretty cool

I don't think any salutations are protected by US law.

They might be here in the UK

would not shock me at all

Case and point, I am frequently known as "Dr. Supreme President Leader Sri Sri The Great Evan "The Great" Carroll (GBE)"

I like to think of myself as the Flagship Personality of the Internets.

Well you're at least the flagship personality of The Heap

I hereby declare you wrong, by fiat.

Well I declare correct by fiat. And we all know fiat can just create new correctness when there isn't any to begin with

Counter revolutionary slander.