Grant Permissions using Dynamic SQL

T Hoecherl
T Hoecherl used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
IT Engineer
Distinguished Expert 2017
Commented:
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.
T HoecherlDeveloper

Author

Commented:
Sorry, my mistake when I posed the question.  I actually am using parentheses.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
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.
T HoecherlDeveloper

Author

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.
T HoecherlDeveloper

Author

Commented:
Thank you

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial