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!

Improve this question

1 Answer 1

Reset to default
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
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.