EXEC security question

I am not an expert with SQL security first of all.
I have a sql user account setup and I want to grant
database wide SP EXEC access, is there a way to do this
or do I have to grant each SP EXEC access on that users
account?On 2005, you can grant execute permissions on schema and also database level. No such option in
2000, though.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Chris Patten" <ChrisPatten@.discussions.microsoft.com> wrote in message
news:D8B55EF6-FBAB-4115-B219-0325A1998D6B@.microsoft.com...
>I am not an expert with SQL security first of all.
> I have a sql user account setup and I want to grant
> database wide SP EXEC access, is there a way to do this
> or do I have to grant each SP EXEC access on that users
> account?|||Thanks, that makes sense.
On a related note. I have given persmission to the user to EXEC on a SP.
It works, but it keeps dropping the permission back to do not allow. So
he'll run the SP from a VBS file and it works fine, but if we try to run it
again it fails, and when we check the permissions, it is off again, ever
heard of that?
"Tibor Karaszi" wrote:
> On 2005, you can grant execute permissions on schema and also database level. No such option in
> 2000, though.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Chris Patten" <ChrisPatten@.discussions.microsoft.com> wrote in message
> news:D8B55EF6-FBAB-4115-B219-0325A1998D6B@.microsoft.com...
> >I am not an expert with SQL security first of all.
> >
> > I have a sql user account setup and I want to grant
> > database wide SP EXEC access, is there a way to do this
> > or do I have to grant each SP EXEC access on that users
> > account?
>|||> On a related note. I have given persmission to the user to EXEC on a SP.
> It works, but it keeps dropping the permission back to do not allow. So
> he'll run the SP from a VBS file and it works fine, but if we try to run it
> again it fails, and when we check the permissions, it is off again, ever
> heard of that?
Did you check that the GRANT you performed really disappears? Or did you draw the conclusion that it
is gone from the fact that the user cannot perform the action anymore? First step is to see what has
happened. Then one might investigate why or who did it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Chris Patten" <ChrisPatten@.discussions.microsoft.com> wrote in message
news:655A640F-7AF4-41D0-B720-8876FEA72F94@.microsoft.com...
> Thanks, that makes sense.
> On a related note. I have given persmission to the user to EXEC on a SP.
> It works, but it keeps dropping the permission back to do not allow. So
> he'll run the SP from a VBS file and it works fine, but if we try to run it
> again it fails, and when we check the permissions, it is off again, ever
> heard of that?
> "Tibor Karaszi" wrote:
>> On 2005, you can grant execute permissions on schema and also database level. No such option in
>> 2000, though.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Chris Patten" <ChrisPatten@.discussions.microsoft.com> wrote in message
>> news:D8B55EF6-FBAB-4115-B219-0325A1998D6B@.microsoft.com...
>> >I am not an expert with SQL security first of all.
>> >
>> > I have a sql user account setup and I want to grant
>> > database wide SP EXEC access, is there a way to do this
>> > or do I have to grant each SP EXEC access on that users
>> > account?
>>|||After we received he permission denied error we went into enterprise mngr and
the EXEC privlege was cleared. Where as before it was checked. I don't see
how or why it would clear itself?
"Tibor Karaszi" wrote:
> > On a related note. I have given persmission to the user to EXEC on a SP.
> > It works, but it keeps dropping the permission back to do not allow. So
> > he'll run the SP from a VBS file and it works fine, but if we try to run it
> > again it fails, and when we check the permissions, it is off again, ever
> > heard of that?
> Did you check that the GRANT you performed really disappears? Or did you draw the conclusion that it
> is gone from the fact that the user cannot perform the action anymore? First step is to see what has
> happened. Then one might investigate why or who did it.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Chris Patten" <ChrisPatten@.discussions.microsoft.com> wrote in message
> news:655A640F-7AF4-41D0-B720-8876FEA72F94@.microsoft.com...
> > Thanks, that makes sense.
> >
> > On a related note. I have given persmission to the user to EXEC on a SP.
> > It works, but it keeps dropping the permission back to do not allow. So
> > he'll run the SP from a VBS file and it works fine, but if we try to run it
> > again it fails, and when we check the permissions, it is off again, ever
> > heard of that?
> >
> > "Tibor Karaszi" wrote:
> >
> >> On 2005, you can grant execute permissions on schema and also database level. No such option in
> >> 2000, though.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "Chris Patten" <ChrisPatten@.discussions.microsoft.com> wrote in message
> >> news:D8B55EF6-FBAB-4115-B219-0325A1998D6B@.microsoft.com...
> >> >I am not an expert with SQL security first of all.
> >> >
> >> > I have a sql user account setup and I want to grant
> >> > database wide SP EXEC access, is there a way to do this
> >> > or do I have to grant each SP EXEC access on that users
> >> > account?
> >>
> >>
>|||Maybe there is a REVOKE or DENY coded into the procedure?
I've seen procedure developers mistakenly put a GRANT or REVOKE
statement at the end of their proc (overlooking the need for a BEGIN
END block or a GO statement).