SQL Server GRANT EXECUTE TO [Role] During Stored Procedure Creation

Why is it so troublesome to find the simple things?

For some time now I've wanted to automatically grant execute permissions to a specific database role during the creation of my stored procedures (for a given app). No, I don't want to grant permissions to a set of existing procedures. I grew accustomed to doing this when I created Oracle Stored Procs a few years ago and tried several times with similar syntax to no avail.
Well I'm about to create a sh*tload of pros and the extra time (and frustration) it takes to add and grant execute on each sp through the management studio was enough to get me to look for the solution again.

I already create enough procedures to warrant the creation of a temp txt file to hold a new proc template:

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
-- ==============================================================
-- Author: Brian H Donoho
-- Create date:
-- Description:
-- ==============================================================

CREATE PROCEDURE [dbo].[usp_]

AS

BEGIN



END

Looking here, the syntax is pretty much what I'd tried using at the bottom of the CREATE PROCEDURE.

Of course it was one tiny little thing coming between me and my goal. A couple of well placed GOs.

This Works:


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
-- ==============================================================
-- Author: Brian H Donoho
-- Create date:
-- Description:
-- ==============================================================

CREATE PROCEDURE [dbo].[usp_]

AS

BEGIN



END
GO

GRANT EXECUTE ON [dbo].[usp_] TO [Role]
GO

SQL Server 2005 Compatibility level and CROSS APPLY

As usual, it took me WAY too long to find/figure this out so I'm sharing a Summary.

The requirement: A User Defined Function that returns one row, but multiple columns. Sounds like something between a scalar and a table valued function, eh?

My first and best hit came from here... except it didn't work X@

Here's a generalized version of my code.

FUNCTION:

CREATE FUNCTION [dbo].[udf_MyUserDefinedFunction] (
@Param1 int
, @Param2 int
)
RETURNS @Params Table (
Column1 int
, Column2 int
, Column3 int
)
AS
BEGIN
DECLARE @Column1 int
DECLARE @Column2 int
DECLARE @Column3 int

SET @Column1 = (SELECT SUM(SomeIntColumn) FROM tblNumbers1 WHERE SomeColumn = @Param1 AND SomeOtherColumn = @Param2)

SET @Column2 = (SELECT
IntColumn1
+ IntColumn3
+ IntColumn4
- IntColumn9
FROM tblNumbers2 WHERE SomeColumn = @Param1 AND SomeOtherColumn = @Param2)

SET @Column3 = (@Column1/@Column3)

INSERT INTO
@Params
VALUES (
@Column1
, @Columns2
, @Column3
)

RETURN
END

-- Created Successfully --

QUERY:

SELECT
x.Column1
, x.Column2
, y.Column1
, y.Column2
, y.Column3
FROM
dbo.SomeTableOrView AS x
CROSS APPLY
dbo.udf_MyUserDefinedFunction(73, x.Column2) AS y


Messages:
.Net SqlClient Data Provider: Msg 102, Level 15, State 1, Line
Incorrect Syntax near 'x'.

The Problem? The server I'm running this code on was upgraded from SQL Server 2000 to 2005. The Compatibility level (Database Properties > Options) was set to SQL Server 2000 (80).

After hours of searching for functional examples of CROSS APPLY (which looked a lot like my code) I looked for info on the error message. This was one of the least helpful error messages I've encountered. I got hits on creating endpoints / web services but eventually stumbled on this.

PS For some reason, the second link is not working in Firefox, but has no problem in IE. WTF?
Well, you already know the answer, I just want to give proper credit.