Una Sp per dare diritti di esecuzione su Stored Procedure e User defined Functions

In .NET quando si lavora su DB SQL Server con molte tabelle, il numero di Stored procedure (SP) sul database può divenire piuttosto grande, se poi sfruttiamo appieno le possibilità di SQL Server aggiungendo User Defined Function (UDF) ed ulteriori SP per fare elaborazioni, query complesse, Pivot e quant’altro, il numero diviene importante.

Se aggiungiamo nuove SP al DB in produzione oppure se abbiamo l’abitudine di Cancellare e Rigenerare tali oggetti quando facciamo un aggiornamento software, gli utenti del nostro DB (A cui ovviamente avremo dato permessi minimi, come ad esempio db_datareader e db_datawriter) non avranno diritto di esecuzione sulle SP e UDF aggiunte o rigenerate, pertanto è indispensabile per noi dare loro questi permessi nel modo più semplice ed indolore possibile.

Questa SP, Made in Luca Del Mestre (esimio collega e DBA oracle) piuttosto grezza ma certamente pronta ad essere resa più sofisticata quando necessario, da il permesso di EXECUTE su tutte le SP utente di un Database e il permesso di SELECT su tutte le UDF.

USE [MyDb]
GO
/****** Object:  StoredProcedure [dbo].[SpGrantExecSpAndFn]    Script Date: 06/19/2008 11:53:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SpGrantExecSpAndFn]
    @Utente nvarchar(128)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @Comando nvarchar(1000);
    DECLARE Rs cursor FOR 
        select 'GRANT EXECUTE ON ' + name + ' TO ' + @Utente +';' 
             Comando from sys.objects where [type] IN ( 'P', 'FN' )
            union
        select 'GRANT SELECT ON ' + name + ' TO ' + @Utente +';' 
             Comando from sys.objects where [type] IN ( 'U',  'IF', 'TF', 'V' )

    OPEN Rs
    FETCH NEXT FROM Rs INTO @Comando

    WHILE @@FETCH_STATUS = 0 BEGIN
        PRINT @Comando 
        Exec(@Comando)
        FETCH NEXT FROM Rs INTO @Comando
    END

    CLOSE Rs
    DEALLOCATE Rs
 
    END

Il Parametro @Utente può essere il nome di un utente del DB oppure il nome di un Ruolo Utente che può essere generato all’interno del DB e mappato su tutti gli utenti che necessitano di connettersi al nostro Database ed utilizzano SP e UDF.