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).

exec permision for sql user

I have a sqlserver user in my DB.I need to grant that user executions permission on all the stored procedures existing in the DB and alos should get the exec permission on newly created SPs in future.How can i achieve this .
Thanks.There is no builtin role for executing stored procedures. You could make the user db_owner, but that will bring in a load of other permissions that you probably would not want the user to have. In order to solve the immediate problem, you could run this query, to generate all the grant statements:

select 'grant execute on ' + SPECIFIC_SCHEMA + '.' + SPECIFIC_NAME + ' to role'
from INFORMATION_SCHEMA.ROUTINES|||hey, thanks for the information.
what does it mean 'SPECIFIC_SCHEMA ' and specific_name' in the above query.|||Those are the two columns you need from the INFORMATION_SCHEMA.Routines view. You should be able to cut and paste the code as is. The only thing you have to change is the name of the user or role you are granting the permissions to.