Hello,
I am writing a script to set the permissions for a particular user.
While looping through the objects, setting the permissions to a multistatement table-valued function is not working.
Can I only set the SELECT permission for such a function?
Thanks in advance.
Regards Hans Heijstee
This is a piece of my code:
FETCH NEXT FROM CURObjects INTO @sObjName
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
SET @sSQL = 'GRANT EXECUTE ON ' + RTRIM(@sObjName) + ' TO ' + @sUser
EXEC sp_executesql @sSQL
END TRY
BEGIN CATCH
if @@ERROR = 4606
BEGIN
BEGIN TRY
SET @sSQL = 'GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE ON ' + RTRIM(@sObjName) + ' TO ' + @sUser
EXEC sp_executesql @sSQL
END TRY
BEGIN CATCH
print @sObjName + ' ' + error_message()
END CATCH
END
END CATCH
FETCH NEXT FROM CURObjects INTO @sObjName
END