[dba-SQLServer] Securables in SQL Server 2005

Liz,

Here is what I think will be a better solution next time
you have this problem.  This is an SP that you can add to
a database and then run.  You pass in the user/role that
you want to grant EXECUTE rights to and it goes through
the SPs and functions and grants the rights.

(SQL 2005)

CREATE PROCEDURE dbo.__SetProcPermissions
     @Role varchar(50)
AS
BEGIN
         SET NOCOUNT ON;

     DECLARE
         @ProcName varchar(100),
         @ProcType varchar(100),
         @Sql varchar(1000)

     DECLARE cProcs CURSOR
     FOR
     SELECT sys.objects.name AS ProcName,
         sys.objects.type_desc AS ProcType
     FROM sys.objects
     WHERE type_desc = 'SQL_STORED_PROCEDURE'
         OR type_desc LIKE '%FUNCTION%'

     OPEN cProcs

     FETCH NEXT
     FROM cProcs
     INTO @ProcName,
         @ProcType

     WHILE (@@FETCH_STATUS = 0)
         -- it still is getting records
         BEGIN
             SELECT @Sql = 'GRANT EXECUTE ON '
                 + @ProcName + ' TO ' + @Role

             EXEC sp_ExecuteSQL @Sql

             IF (CHARINDEX(@ProcType,'FUNCTION', 1) > 0)
                 BEGIN
                     SELECT @Sql = 'GRANT REFRENCES ON '
                         + @ProcName + ' TO ' + @Role

                     EXEC sp_ExecuteSQL @Sql
                 END

             FETCH NEXT
             FROM cProcs
             INTO @ProcName,
                 @ProcType
         END

     CLOSE cProcs
     DEALLOCATE cProcs

END
GO

I guess it would help if I actually sent it.  :-)

Robert


At 01:00 PM 3/16/2007, you wrote:
>Date: Fri, 16 Mar 2007 08:15:40 -0500
>From: <Elizabeth.J.Doering at wellsfargo.com>
>Subject: Re: [dba-SQLServer] Securables in SQL Server 2005
>To: <dba-sqlserver at databaseadvisors.com>
>Message-ID:
> 
><1C2084FD2472124AB1812A5476EA3B7A014836EB at msgswbmnmsp04.wellsfargo.com>
>
>Content-Type: text/plain;       charset="US-ASCII"
>
>Francisco,
>
>Thanks so much for replying.  I actually came to the same solution.
>
>Since, however, I had hundreds to set--and a bunch to unset--I created a
>table with all the objects and who needed permissions on each one.  Then
>I built up all my Grant and Revoke statements in a query, pasted the
>results in a new query window and ran them all at once.  I'm sure there
>were more elegant solutions, but this one did save my fingers--and my
>hair!
>
>Thanks,
>
>Liz