GRANT EXEC Problem

Hi,
SQL Server does not like my stored procedure below. It complains
about the GRANT EXEC line
What am I doing wrong?
JD
----
DECLARE @.PROCNAME varchar(50)
DECLARE cPROCNAME CURSOR FOR
SELECT NAME
FROM SYSOBJECTS
WHERE XTYPE = 'P' AND CATEGORY = 0
ORDER BY NAME
OPEN cPROCNAME
FETCH NEXT FROM cPROCNAME INTO @.PROCNAME /* Prime the cursor */
WHILE @.@.FETCH_STATUS = 0
BEGIN
GRANT EXEC ON @.PROCNAME TO PUBLIC <--Problem here
FETCH NEXT FROM cPROCNAME INTO @.PROCNAME
END
CLOSE cPROCNAME
DEALLOCATE cPROCNAMEYou cannot do a grant on a variable. You'll have to use dynamic SQL. (BTW,
in SQL 2005, you can do a GRANT EXEC on an entire SCHEMA)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Joe Delphi" <delphi561@.nospam.cox.net> wrote in message
news:JZLff.5127$xu.344@.fed1read01...
Hi,
SQL Server does not like my stored procedure below. It complains
about the GRANT EXEC line
What am I doing wrong?
JD
----
DECLARE @.PROCNAME varchar(50)
DECLARE cPROCNAME CURSOR FOR
SELECT NAME
FROM SYSOBJECTS
WHERE XTYPE = 'P' AND CATEGORY = 0
ORDER BY NAME
OPEN cPROCNAME
FETCH NEXT FROM cPROCNAME INTO @.PROCNAME /* Prime the cursor */
WHILE @.@.FETCH_STATUS = 0
BEGIN
GRANT EXEC ON @.PROCNAME TO PUBLIC <--Problem here
FETCH NEXT FROM cPROCNAME INTO @.PROCNAME
END
CLOSE cPROCNAME
DEALLOCATE cPROCNAME