Hello everyone in the community,
Our project is running in a self-managed environment with SQL Server installed.
Currently, we have two DB catalogs:
master: The required DB for the self-managed environment (outsystems_DEV)
External: A business database connected and used through Integration Studio (textdb)
What I would like to do is call a SQL Server user-defined function from an OutSystems SQL widget.
The issue I am experiencing is that when I call dbname.dbo.FN_FUNCTION from the SQL widget, I receive an error stating that there is no permission to EXECUTE FN_FUNCTION.
I found that queries executed from the SQL widget are run under the 'OUTRUN_DEV' user in the 'master' DB catalog, so I granted the 'OUTRUN_DEV' user permission to call dbname.dbo.FN_FUNCTION.
However, I am still receiving the same error.
I would like to know how to call a user-defined function from an additionally created database through an OutSystems SQL widget.
@Yongsu Lee : As far as I understand, the EXECUTE permission needs to be granted to the user configured for the external database connection in Service Center -> Administration -> Database Connections. In the screenshot, the database connection is using the user demouser, so the EXECUTE permission should be granted to demouser, not OUTRUN_DEV.
A detailed instruction on how to setup is given at https://www.outsystems.com/forums/discussion/14867/how-to-execute-stored-procedures/
Hi @Yongsu Lee,
I think what's worth checking first is whether OUTRUN_DEV itself even has visibility into the external database not just whether the function specifically is blocked.
Try logging into SQL Server Management Studio using the OUTRUN_DEV login itself. Once connected as OUTRUN_DEV, run something simple like:
SELECT * FROM textdb.dbo.SomeTable;
So in short: confirm OUTRUN_DEV can see textdb at all first, logged in as that exact login then, if that checks out, you can dig into permissions on that one function specifically.
If possible, you might be able to skip most of this instead of granting broad table/database access, you can scope permissions down to just FN_FUNCTION itself and consuming it using Integration studio, nothing else in textdb.