Grant EXEC to SPROCS

I want to grant exec permission to a windows account <domain\user> to 150
stored procedures that are prefixed with usp_ . How do I script this so that
i won't have to do them individually?>I want to grant exec permission to a windows account <domain\user> to 150
> stored procedures that are prefixed with usp_ . How do I script this so
> that
> i won't have to do them individually
Try a script like the one below.
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 SpExecuteRole'
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_NAME LIKE '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
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:FAD323C1-40A9-46C6-B9EC-F02CFDF3E4B7@.microsoft.com...
>I want to grant exec permission to a windows account <domain\user> to 150
> stored procedures that are prefixed with usp_ . How do I script this so
> that
> i won't have to do them individually?