Definir permissão para Stored Procedures no SQL Server

Muitas pessoas têm me perguntado, recentemente, como definir permissões de execução para todas as Stored Procedures de um Banco de Dados SQL Server 2005/2008 de maneira automática, sem que seja necessário utilizar a interface gráfica do SQL Management Studio que requer que você defina as permissões das SPs uma por uma.

No SQL Server, para definir permissão de execução para uma Stored Procedure tudo o que você precisa fazer é utilizar a sintaxe:

GRANT EXECUTE ON [dbo].[sproc] TO [Usuário]

* [dbo].[sproc] é o nome da Stored Procedure
* [Usuário] é o nome da conta de usuário que receberá a permissão.

Entretanto, durante o desenvolvimento de um sistema computacional com um mínimo de complexidade, são criadas inúmeras Stored Procedures, o que torna a tarefa de executar a permissão em massa para os procedimentos armazenados uma atividade cansativa.

Para resolver este problema, preparei o seguinte script que irá gerar, automaticamente, os comandos GRANT para todas as Stored Procedures definidas no seu Banco de Dados:

-- Thiago Marotta Couto
-- October, 04 - 2009
-- http://isbyte.com/	

DECLARE @userName AS NVARCHAR(100);
DECLARE @namePattern AS NVARCHAR(10);

	-- Nome do Usuário
	SET @userName = N'livebackup';

	-- Filtrar Stored Procedures
	-- Exemplos:
	--		%	Todas Stored Procedures
	--		spc%	Todas Stored Procedures que começam com "spc"
	--		%SP	Todas Stored Procedures que terminam com "SP"
	SET @namePattern = N'%';

	-- Define o Banco de Dados
	USE [master]

	SELECT
		N'GRANT EXECUTE ON '
		+ QUOTENAME(OBJECT_SCHEMA_NAME([object_id])) + '.'
		+ QUOTENAME([name])
		+ N' TO '
		+ QUOTENAME(@userName)
		+ N';'
	FROM sys.procedures
	WHERE [name] LIKE @namePattern;

Após executar o script, copie o resultado (lista de GRANTs) e execute na sua base de dados.