Opinions Requested:
I have a situation at work where a programmer is wanting to use the following script to determine if a user is allowed to run a specific set of stored procedures or not and I’m concerned because he is using system tables to get this information and I’ve had it drilled into me not to use the system tables other than my own DBA scripts…
I was hoping to hear the different opinions on this so that I can give the programmer some intelligent feedback. I’ll split the TechPoints with the best 2 replies I get within the next week or so…
Thanks in advance!
Jeff M. Belina
—- cut here —-
SELECT
ProcName =so.name,
userName = su.name
FROM
sysobjects so (NOLOCK),
sysprotects sp (NOLOCK),
sysusers su (NOLOCK)
WHERE
so.type = ‘P’
and sp.action = 224
and sp.id = so.id
and sp.uid = su.gid
and su.name = USER_NAME()
