Grant Permissions using Dynamic SQL

I have some code in a stored procedure that looks like this:

SELECT @SQL = 'GRANT SELECT, UPDATE, INSERT, DELETE ON' + @TABLENAME + ' TO ' @USER + ' GO'
EXECUTE @SQL

@TABLENAME has previously been created and is named as 'PCT_SALES_DISTREF_' + @USER.  @USER is provided as an input parameter to the stored procedure. When I run the procedure I get the error message on the attached file.

Grant-Permissions-Error.docx

SQL is looking at the GRANT statement as a stored procedure.  The problem is in the Dynamic SQL, because if I just hardcode that line as

GRANT SELECT, UPDATE, INSERT, DELETE ON PCT_SALES_DISTREF_test TO test, it runs without error.  Can you see what the problem may be in my Dynamic SQL code?
T HoecherlDeveloperAsked:
Who is Participating?
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You need to use parenthesis:
SELECT @SQL = 'GRANT SELECT, UPDATE, INSERT, DELETE ON' + @TABLENAME + ' TO ' @USER
EXEC(@SQL)

Open in new window

NOTE: You don't need the GO keyword.
0
 
T HoecherlDeveloperAuthor Commented:
Sorry, my mistake when I posed the question.  I actually am using parentheses.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
For me it's working. Better you re check as I can see it's missing a space after ON and also missing a plus sign before @USER variable.
0
 
T HoecherlDeveloperAuthor Commented:
My mistake again, Vitor.  I just went back and checked and I was, in fact, missing the parentheses (I know better than that.  It was a foolish mistake?.  Thank you for your help.
0
 
T HoecherlDeveloperAuthor Commented:
Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.