SQL Canada (SQLCAN)

Following code can be used to Grant Execute Permissions to a Database Role, in a database. This is for SQL Server 2000; because in 2000 you can’t just Grant Execute on a Schema or Database like we can in SQL Server 2005/2008.

EXEC dbo.sp_addrole @rolename = N'udbr_SPPermissions'
GO

EXEC dbo.sp_addrolemember N'udbr_SPPermissions', N'DOMAIN\user'
GO

 SELECT 'GRANT EXECUTE ON ' + name + ' TO udbr_SPPermissions' AS SQLStatement
   INTO #NewPermissions
   FROM sysobjects
  WHERE xtype = 'P'
    AND ( name LIKE 'sp[_]%' OR
          name LIKE 'up[_]%')
ORDER BY name
GO

DECLARE @SQLStatement VARCHAR(512)

DECLARE SPPermissions CURSOR FOR
SELECT SQLStatement
  FROM #NewPermissions

OPEN SPPermissions

  FETCH NEXT
   FROM SPPermissions
   INTO @SQLStatement

  WHILE @@FETCH_STATUS = 0
  BEGIN

     PRINT 'Executing: ' + @SQLStatement
     EXEC (@SQLStatement)

     FETCH NEXT
      FROM SPPermissions
      INTO @SQLStatement

  END

CLOSE SPPermissions
DEALLOCATE SPPermissions
GO