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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
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.
ASKER
Thank you
ASKER