Changing Permissions for all Stored Procs in a SQL2005 DB

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). Doin
g
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.
AlexHi,
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@.newsgroup.nospam> wrote in message
news:1BCFF9ED-12DC-4FD3-921F-3A1E185DB18E@.microsoft.com...
>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|||Hi Alex,
Thanks for posting.
To execute a stored procedure, you have to be granted execute permission on
this stored procedure. As we know, stored procedure referenced as object in
SQL 2005. To grant permission on a object, we have a T-SQL command: Grant.
You can Grants permissions on a table, view, table-valued function, stored
procedure, extended stored procedure, scalar function, aggregate function,
service queue, or synonym.
For more information about Grant command, please refer to following link:
http://msdn2.microsoft.com/en-us/library/ms188371.aspx
Hope this information helps.
Best regards,
Vincent Xu
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
--[vbcol=seagreen]
Doing[vbcol=seagreen]
another[vbcol=seagreen]
sample?[vbcol=seagreen]|||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", wil
l
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@.newsgroup.nospam> wrote in message
> news:1BCFF9ED-12DC-4FD3-921F-3A1E185DB18E@.microsoft.com...
>
>|||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@.newsgroup.nospam> wrote in message
news:E57C2633-AE66-4837-8769-A33CB3C36CE1@.microsoft.com...[vbcol=seagreen]
> 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:
>|||> 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?
One method is to generate a script to do this. The example below was
developed for SQL 2000 but works with SQL 2005 too.
SET NOCOUNT ON
DECLARE @.GrantStatement nvarchar(4000)
DECLARE GrantStatements CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT
N'GRANT EXECUTE ON ' +
QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME) +
N' TO MyNewRole'
FROM INFORMATION_SCHEMA.ROUTINES
WHERE
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsMSShipped') = 0 AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsProcedure') = 1 AND
ROUTINE_SCHEMA = N'dbo' AND
ROUTINE_NAME LIKE N'usp%'
OPEN GrantStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM GrantStatements
INTO @.GrantStatement
IF @.@.FETCH_STATUS = -1 BREAK
BEGIN
RAISERROR (@.GrantStatement, 0, 1) WITH NOWAIT
EXECUTE sp_ExecuteSQL @.GrantStatement
END
END
CLOSE GrantStatements
DEALLOCATE GrantStatements
Hope this helps.
Dan Guzman
SQL Server MVP
"Alex Maghen" <AlexMaghen@.newsgroup.nospam> wrote in message
news:E57C2633-AE66-4837-8769-A33CB3C36CE1@.microsoft.com...[vbcol=seagreen]
> 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:
>