In .NET quando si lavora su DB SQL Server con molte tabelle, il numero di Stored procedure sul database diviene piuttosto grande, se poi sfruttiamo appieno le possibilità di SQL Server aggiungendo User Defined Function ed ulteriori SP per fare elaborazioni, query complesse, Pivot e quant'altro, il numero diviene importante.
Se aggiungiamo nuove stored procedure 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 Stored procedure aggiunte o rigenerate, pertanto è indispensabile per noi dare loro questi permessi nel modo più semplice ed indolore possibile.
Questa Stored procedure, 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 User Defined Functions.
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
-- =============================================
-- Author: Sabrina C.
-- Create date: 18/06/2008
-- Description: Stored procedure per assegnare diritti di esecuzione SP + FN ad un utente o Ruolo
-- =============================================
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 Stored Procedure e User Defined Functions.
Tags: SQL, Query, StoredProcedure, SqlServer