SQL 2k SP Security? [Resolved]

[RobDog888]

I cant test this at the moment, but if I have a userdefined sp in
my db and I make a nested call to a system sp in the master db,
will my users have any issues executing the user defined sp if
they only have permissions assigned to it and not the nested
system sp below?

In other words, will the security permissions cascade down to the
system sp?

Thanks.

[vb_dba]

System SP's in Master by default grant exec permissions to public. If these permissions have been changed, then you WILL need to grant exec permission on the system sp's in master to the users that need to execute them.

[si_the_geek]

I checked BOL but I cant find anything useful, I think that nested SP's execute with higher permissions than the user who ran the original SP (I used xp_sendmail like that when we had denied permission on it).

[szlamany]

I am under the impression that you only grant access to the USER SP that is being called and whatever it does inside is automatically granted.

In other words - the permissions cascade.

I'm 95% sure anyway...

[brucevde]

The answer, like so many others, is "it depends".

Depends on the system stored procedure that you are calling and it depends on the contents of the stored procedure.

I am assuming you are using Exec. Here is some info from BOL.

Permissions
EXECUTE permissions for a stored procedure default to the owner of the stored procedure, who can transfer them to other users. Permissions to use the statement(s) within the EXECUTE string are checked at the time EXECUTE is encountered, even if the EXECUTE statement is included within a stored procedure. When a stored procedure is run that executes a string, permissions are checked in the context of the user who executes the procedure, not in the context of the user who created the procedure. However, if a user owns two stored procedures in which the first procedure calls the second, then EXECUTE permission checking is not performed for the second stored procedure

[RobDog888]

Basically I am wrapping the sp_who sp with one of my own. I thought
it would be better to call it this way, but if not then I would just
have the code call it from the master db. My way allows me to
assign permissions to my sp and hopefully the permissions would
cascade down to the sp_who system sp.

Is this correct then?

Thanks.

[szlamany]

Can't you just rip to SPROC source from the MASTER SPROC and put it into your own SPROC - so that you don't have to call the MASTER SPROC at all?

I've seen someone suggest this once - never did it myself though...

[RobDog888]

Yes, I guess I could. I was just trying to keep it simple, but I made
it more complicated then it should of been.

Thanks.

[brucevde]

As long as the user has permissions to execute your stored procedure they will have the permission to execute the sp_who procedure. sp_who, by default, is available to everyone.

It is not recommended to query the system tables directly but that is entirely up to you.

[RobDog888]

So then how should I call it or just copy it over to my db?

[brucevde]

You could just call the sp_who procedure directly, otherwise something like this would also work.

Create Procedure dbo.ShowWhosOnline

As

Exec sp_who

[RobDog888]

That is how I originally was doing it and I was wondering about
any permissions needed if the permissions dont cascade down to
the sp_who sp.