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 (http://blog.SQLAuthority.com)

6 thoughts on “SQL SERVER – Read Only Files and SQL Server Management Studio (SSMS)

  1. 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.


  2. Pingback: SQL SERVER – Remove Debug Button in SSMS – SQL in Sixty Seconds #020 – Video « SQL Server Journey with SQL Authority

  3. 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


  4. Pingback: SQL SERVER – Weekly Series – Memory Lane – #034 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s