Grant Execute Permission to User in Database:Sql Servers

---Stored Procedure to grant execute permission to user on all stored procedures

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spGrantExectoAllStoredProcs] @user sysname

AS
/

SET NOCOUNT ON



DECLARE @CMD1 varchar(8000)

DECLARE @MAXOID int

DECLARE @OwnerName varchar(128)

DECLARE @ObjectName varchar(128)



CREATE TABLE #StoredProcedures

(OID int IDENTITY (1,1),

StoredProcOwner varchar(128) NOT NULL,

StoredProcName varchar(128) NOT NULL)



INSERT INTO #StoredProcedures (StoredProcOwner, StoredProcName)

SELECT u.[Name], o.[Name]

FROM dbo.sysobjects o

INNER JOIN dbo.sysusers u

ON o.uid = u.uid

WHERE o.Type = 'P'

AND o.[Name] NOT LIKE 'dt_%'


SELECT @MAXOID = MAX(OID) FROM #StoredProcedures



WHILE @MAXOID > 0

BEGIN



SELECT @OwnerName = StoredProcOwner,

@ObjectName = StoredProcName

FROM #StoredProcedures

WHERE OID = @MAXOID



SELECT @CMD1 = 'GRANT EXEC ON ' + '[' + @OwnerName + ']' + '.' + '[' + @ObjectName + ']' + ' TO ' + @user


EXEC(@CMD1)



SET @MAXOID = @MAXOID - 1

END



DROP TABLE #StoredProcedures

SET NOCOUNT OFF