SQL SERVER – Read Only Files and SQL Server Management Studio (SSMS)

Just like any other Developer or DBA SQL Server Management Studio is my favorite application. Any any moment of the time I have multiple instances of the same application are open and I am working on it. Recently, I have come across a very interesting feature in SSMS related to “Read Only” files. I believe it is a little unknown feature as well so decided to write a blog about the same.

First create a read only SQL file. You can make any file read by Right Click >> Properties >> Select Attribute Read Only.

Now open the same file in SQL Server Management Studio. You will find that besides the file name there is a small ‘lock’ icon. This small icon indicates that the file is read only.

Now let us attempt to edit the read only file. It will let us edit the file any way we want, however when we attempt to save it, it gives following pop-up value.

The options in the pop-up are self explanatory and I liked it. The goal of the read only file is to prevent users to make un-intended changes. However, when a user should have complete control over the user file. User should be aware that the file is read only but if he wants to edit the file or save as a new file the choices should be present in front of it and the pop-up menu precisely captures the same.

Now let us check option related to this feature in SSMS. Go to Menu >> Options >> Environment >> Documents

You will find the third option which is “Allow editing of read-only files; warn when attempt to save”. In the above scenario it was already checked.

Let us uncheck the same and do the same exercise which we have done earlier. I closed all the earlier window to avoid confusion.

With the new option selected when I attempt to even modify the Read Only file, it gives me totally different pop up screen. It gives me an option like “Edit In-Memory”, “Make Writeable” etc.

When you select “Edit In-Memory” it allows you to edit the file and later you can save as new file – just like the earlier scenario which we have discussed. . If clicked on the Make Writeable it will remove the restriction of the Read Only and file can be edited as pleased.

Reference: Pinal Dave (https://blog.sqlauthority.com)