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.

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

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.

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

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.

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

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

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

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.

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

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.

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

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)

Previous Post
SQL SERVER – Simple Explanation and Puzzle with SOUNDEX Function and DIFFERENCE Function
Next Post
SQL SERVER – Identifying Column Data Type of uniqueidentifier without Querying System Tables

Related Posts

4 Comments. Leave new

  • robertmcbean
    June 17, 2012 8:19 am

    Good post. Also, if a data file is compressed by windows file system, it will function as read-only to the server. Data file backups that are compressed by the windows file system cannot be restored. Error messages for these things are not always very clear.

    Reply
  • ChiragSatasiya
    June 19, 2012 5:41 pm

    Hi pinal sir,
    Really informative post.

    Regard$
    Chirag Satasiya

    Reply
  • ChiragSatasiya
    June 19, 2012 5:42 pm

    Hi pinal sir,
    Really small but useful tip.

    Regard$
    Chirag Satasiya

    Reply
  • Hello Pinal, I want to lock SSMS 2008. Is there an easy way to do it? Or should I get a 3rd party tool?

    I want to secure both SQL Admin login and Windows network authentication. Please help

    Best Regards, Bejoy

    Reply

Leave a Reply

Menu