Lesson Learned #329: DATABASEPROPERTYEX( DB_NAME() ,  ‘Updateability’ ) and db_datareader role

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Community Hub.

Today, we got a question where our customer asked that if using ApplicationIntent=ReadWrite with an user with db_datareader permision, the results of DATABASEPROPERTYEX(DB_NAME(), 'Updateability') will be affected or not.

 

In this situation, let's try to create a business critical database with readscale out enabled and create the following user. Right now, the answer is not affected.

 

 

create user UserName with password = 'PasswordX2X3X1!' alter role db_datareader add member UserName

 

 

Once we have established the connection using SQL SERVER Management Studio using this user and execute the query

 

 

SELECT DATABASEPROPERTYEX(DB_NAME(), 'Updateability');

 

 

The results will be:

 

Jose_Manuel_Jurado_0-1676308307894.png

 

However, using applicationIntent=Readonly with the same user the results will be the expected one:

 

Jose_Manuel_Jurado_1-1676308371449.png

 

Additionaly, I would like to share an article that explain the behaviour when we are using Transparent Failover Group and ApplicationIntent at the same time - Lesson Learned #131: ReadScale Out and Failover Group - Microsoft Community Hub

 

Enjoy!

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.