asked on 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?
Microsoft SQL Server
Last Comment
T Hoecherl
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Sorry, my mistake when I posed the question. I actually am using parentheses.
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