Azure SQL Managed Instances offer access to read replicas from the availability group (in specific tiers at least).
I am writing a test script for our application to connect to read/write and read only connections to prove everything is functioning as it should.
I know we can use the databasepropertyex SP to identify the current database access level, which will be enough.
However, the application user is obviously limited in permissions – so I want to grant the lowest possible permissions to the application user that still allows that user to identify the connectivity type.
I am really struggling to identify what permissions I need to grant to the app user that will allow them to see the output of this command – currently it returns 'NULL' regardless of rights I have granted.
I have obviously missed something somewhere!
Best Answer
It appears that the issue was not permissions at all.
Thanks to this thread https://lists.ibiblio.org/pipermail/freetds/2010q2/025616.html I was pointed in the correct direction - our connection library was not able to handle the return type for the procedure gracefully, as it couldn't map the datatype.
SELECT CAST(DATABASEPROPERTYEX(DB_NAME(), 'Updateability') AS VARCHAR(40));
Returns the expected output.
For reference, to confirm the permissions that worked for us;
SQL User
public
public
,db_datareader
,db_datawriter