0

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!

2

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

  • Server Roles - public
  • Database Roles - public, db_datareader, db_datawriter

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.