EXECUTE permissions

I need to grant a user execute permissions on 1000 stored procedures. This
there any easy way to do this in SQL 2005?IF its 1000 procs among many procs, then you can do it the old fashioned way
.
execute this
select 'grant exec on ' + name + ' to <user_name>; go' from sysobjects where
type = 'p' and <your other filters here>
copy paste the result set and execute the result set. Hope this helps|||I know the old way, but I was hoping they had made it easier in 2005.
"Omnibuzz" wrote:

> IF its 1000 procs among many procs, then you can do it the old fashioned w
ay.
> execute this
> select 'grant exec on ' + name + ' to <user_name>; go' from sysobjects whe
re
> type = 'p' and <your other filters here>
> copy paste the result set and execute the result set. Hope this helps
>|||Do you have any particular way in mind that you are looking for.
Can you elaborate on the exact requirement.
Like doing it automatically and not have the manual intervention of copying
and pasting the result set. In that case you can use XP_EXECRESULTSET to do
it in one shot. it was available in SQL 2000 too.
--
-Omnibuzz
--
Please post ddls and sample data for your queries and close the thread if
you got the answer for your question.
"Andre" wrote:
> I know the old way, but I was hoping they had made it easier in 2005.
> "Omnibuzz" wrote:
>|||If you grouped them in the same schema, you can GRANT execute at the schema,
or even database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andre" <Andre@.discussions.microsoft.com> wrote in message
news:F6D546EB-AB53-4E08-B5A2-7CE746FF16BF@.microsoft.com...
>I need to grant a user execute permissions on 1000 stored procedures. This
> there any easy way to do this in SQL 2005?|||So in AdventureWorks, how would I grant a user execute permissions on all th
e
HumanResource procedures with click on them individually? You said grant
permissions at the schema level, but I cant seem to make it work. If you
could provide step by step how to do this I would be grateful. Thanks.
"Tibor Karaszi" wrote:

> If you grouped them in the same schema, you can GRANT execute at the schem
a, or even database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Andre" <Andre@.discussions.microsoft.com> wrote in message
> news:F6D546EB-AB53-4E08-B5A2-7CE746FF16BF@.microsoft.com...
>
>|||Right-click the schema, properties, permissions, browse the user...
Or execute below:
GRANT EXECUTE ON SCHEMA::schemaname TO username
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andre" <Andre@.discussions.microsoft.com> wrote in message
news:1A9C0F71-9411-4FF5-8365-8C9D6C42B81A@.microsoft.com...
> So in AdventureWorks, how would I grant a user execute permissions on all
the
> HumanResource procedures with click on them individually? You said grant
> permissions at the schema level, but I cant seem to make it work. If you
> could provide step by step how to do this I would be grateful. Thanks.
> "Tibor Karaszi" wrote:
>