Re: Changing Permissions for all Stored Procs in a SQL2005 DB

Hi,

As far as I know you need to give EXEC privilages to indvidual stored
procedures.

sample script:-

Run this in the database, then copy the results to the query window
and execute.

select 'Grant EXEC on ' + name + ' to WhomEver'
from sysobjects
where type = 'P'


Thanks
Hari
SQL Server MVP






"Alex Maghen" <AlexMaghen@xxxxxxxxxxxxxxxx> wrote in message
news:E57C2633-AE66-4837-8769-A33CB3C36CE1@xxxxxxxxxxxxxxxx
Hari -

Hi. This is very helpful. Can I ask one more detail? Assuming that I've
already created the database role, if I do "GRANT EXECUTE TO MyNewRole",
will
this grant execute permissions to ALL of the stored procedures in the
database (including system stored procedures)? I don't really want to do
that. Is there a way that I can have it grant execute only to stored procs
that I created (dbo.<procname>)? Or can I do it by matching a pattern in
the
SP name? All of my stored procedures start with a special 3 character
prefix.
Can I use a "where" clause or something? And if I can, how?

Let me know?

Thanks!

Alex


"Hari Prasad" wrote:

Hi,

In SQL Server 2005 it is very easy to set the execute rights to all
procedures. See the script

USE DBNAME
GO
/* CREATE A NEW ROLE */
CREATE ROLE db_executor

/* GRANT EXECUTE TO THE ROLE */
GRANT EXECUTE TO db_executor

Assign this role to the database user and he will be able to execute all
procedures inside the databse

THanks
Hari
SQL Server MVP



"Alex Maghen" <AlexMaghen@xxxxxxxxxxxxxxxx> wrote in message
news:1BCFF9ED-12DC-4FD3-921F-3A1E185DB18E@xxxxxxxxxxxxxxxx
I have a massive number of Stored Procedures in a SQL Database. I need
to
give a Database Role in that Database Execute permissions for all of
the
Stored Procedures I had created (but not the System Stored Procedures).
Doing
this through the SQL Management Tool UI will take FOREVER. Is there
another
way? If it's a script of some kind, can someone give me a hint or a
sample?
This may be a bit beyond me.

Alex





.

Relevant Pages