[dba-SQLServer] Securables in SQL Server 2005

Thanks Robert!


 
Liz
 
 
 



Liz  Doering
612.667.2447 

"This message may contain confidential and/or privileged information. If
you are not the addressee or authorized to receive this for the
addressee, you must not use, copy, disclose, or take any action based on
this message or any information herein. If you have received this
message in error, please advise the sender immediately by reply e-mail
and delete this message. Thank you for your cooperation"

 
 

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com 
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf 
Of Robert L. Stewart
Sent: Friday, March 16, 2007 2:15 PM
To: dba-sqlserver at databaseadvisors.com
Cc: Doering, Elizabeth J.
Subject: Re: [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


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com