Removing the remembered login and password list in SQL Server Management Studio

Question

I've recently used our company's spare laptop (that has a general user set up) while mine was being repaired. I've checked the "Remember password" option in SQL Server Management Studio when logging in to the database.

I need to clear the login and password information that I have used to prevent the next person that will use the laptop from using my login names and passwords. How can I do this?

Solution 1

Another answer here also mentions since 2012 you can remove Remove cached login via How to remove cached server names from the Connect to Server dialog?. Just confirmed this delete in MRU list works fine in 2016 and 2017.

SQL Server Management Studio 2017 delete the file C:\Users\%username%\AppData\Roaming\Microsoft\SQL Server Management Studio\14.0\SqlStudio.bin

SQL Server Management Studio 2016 delete the file C:\Users\%username%\AppData\Roaming\Microsoft\SQL Server Management Studio\13.0\SqlStudio.bin

SQL Server Management Studio 2014 delete the file C:\Users\%username%\AppData\Roaming\Microsoft\SQL Server Management Studio\12.0\SqlStudio.bin

SQL Server Management Studio 2012 delete the file C:\Users\%username%\AppData\Roaming\Microsoft\SQL Server Management Studio\11.0\SqlStudio.bin

SQL Server Management Studio 2008 delete the file C:\Users\%username%\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell\SqlStudio.bin

SQL Server Management Studio 2005 delete the file – same as above answer but the Vista path. C:\Users\%username%\AppData\Roaming\Microsoft\Microsoft SQL Server\90\Tools\Shell\mru.dat

These are profile paths for Vista / 7 / 8.

EDIT:

Note, AppData is a hidden folder. You need to show hidden folders in explorer.

EDIT: You can simply press delete from the Server / User name drop down (confirmed to be working for SSMS v18.0). Original source from https://blog.sqlauthority.com/2013/04/17/sql-server-remove-cached-login-from-ssms-connect-dialog-sql-in-sixty-seconds-049/ which mentioned that this feature is available since 2012!

Solution 2

This works for SQL Server Management Studio v18.0

The file "SqlStudio.bin" doesn't seem to exist any longer. Instead my settings are all stored in this file:

C:\Users\*********\AppData\Roaming\Microsoft\SQL Server Management Studio\18.0\UserSettings.xml

• Open it in any Texteditor like Notepad++
• ctrl+f for the username to be removed
• then delete the entire <Element>.......</Element> block that surrounds it.

EDIT: An even easier and working solution for v18.0 (Preview 7) would be:

• Go to the "Connect to Server" dialogue window:

  

• Click the down-arrow icon marked green in the screenshot.

• Use the arrow-keys on the keyboard to navigate up/down

• Press the DEL key on keyboard to delete the entry.

• Close the dialogue window and when you reopen it the entry will indeed be removed.

Hope it helps :-)

Solution 3

For those looking for the SSMS 2012 solution... see this answer:

Remove cached login 2012

Essentially, in 2012 you can delete the server from the server list dropdown which clears all cached logins for that server.

Works also in v17 (build 14.x).

Solution 4

In my scenario I only wanted to remove a specific username/password from the list which had many other saved connections I didn't want to forget. It turns out the SqlStudio.bin file others are discussing here is a .NET binary serialization of the Microsoft.SqlServer.Management.UserSettings.SqlStudio class, which can be deserialized, modified and reserialized to modify specific settings.

To accomplish removal of the specific login, I created a new C# .Net 4.6.1 console application and added a reference to the namespace which is located in the following dll: C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\ManagementStudio\Microsoft.SqlServer.Management.UserSettings.dll (your path may differ slightly depending on SSMS version)

From there I could easily create and modify the settings as desired:

using System.IO;
using System.Runtime.Serialization.Formatters.Binary;
using Microsoft.SqlServer.Management.UserSettings;

class Program
{
    static void Main(string[] args)
    {
        var settingsFile = new FileInfo(@"C:\Users\%username%\AppData\Roaming\Microsoft\SQL Server Management Studio\13.0\SqlStudio.bin");

        // Backup our original file just in case...
        File.Copy(settingsFile.FullName, settingsFile.FullName + ".backup");

        BinaryFormatter fmt = new BinaryFormatter();

        SqlStudio settings = null;

        using(var fs = settingsFile.Open(FileMode.Open))
        {
            settings = (SqlStudio)fmt.Deserialize(fs);
        }

        // The structure of server types / servers / connections requires us to loop
        // through multiple nested collections to find the connection to be removed.
        // We start here with the server types

        var serverTypes = settings.SSMS.ConnectionOptions.ServerTypes;

        foreach (var serverType in serverTypes)
        {
            foreach (var server in serverType.Value.Servers)
            {
                // Will store the connection for the provided server which should be removed
                ServerConnectionSettings removeConn = null;

                foreach (var conn in server.Connections)
                {
                    if (conn.UserName == "adminUserThatShouldBeRemoved")
                    {
                        removeConn = conn;
                        break;
                    }
                }

                if (removeConn != null)
                {
                    server.Connections.RemoveItem(removeConn);
                }
            }
        }

        using (var fs = settingsFile.Open(FileMode.Create))
        {
            fmt.Serialize(fs, settings);
        }
    }
}

Solution 5

There is a really simple way to do this using a more recent version of SQL Server Management Studio (I'm using 18.4)

1. Open the "Connect to Server" dialog
2. Click the "Server Name" dropdown so it opens
3. Press the down arrow on your keyboard to highlight a server name
4. Press delete on your keyboard

Login gone! No messing around with dlls or bin files.

View more solutions

Comments

[abatishchev]

"%AppData%\Microsoft\Microsoft SQL Server\100\Tools\Shell\SqlStudio.bin"

[abatishchev]

I have installed MSSSMS2008E under Windows 7 and even have not mru.data neither in %AppData%\Microsoft\Microsoft SQL Server\100\Tools\Shell not in %LocalAppData%\Microsoft\Microsoft SQL Server\100\Tools\Shell. But Robin Luiten's answer helps under both Windows XP and Windows 7. As far as I see our controversy takes place often: tinyurl.com/ybc8x8p

[Bruce]

The mru file may exist in Vista/Win7, but for XP, I never found the mru file. I instead followed the above comment (renamed but did not delete) and Sql Manager happily recreated the SqlStudio.bin file next time I launched it, so the above comment is correct for XP users.

[IsmailS]

+1 Thanks! Removing C:\Users\%username%\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell\SqlStudio.bin worked for me in Win7.

[ChandlerPelhams]

+1 My 'remember password' was not working correctly for some reason. Deleting this file allowed me to create the login again and it worked just fine. C:\Documents and Settings\%username%\Application Data\Microsoft\Microsoft SQL Server\100\Tools\Shell\ for Windows XP.

[Vikas]

May be this can be an another question, but I am afraid that it' likely going to close as it could be very product specific. But is there any why to edit the information which is stored in above files? I'd like to remove some saved logins.

[J Bryan Price]

%AppData%\Microsoft\SQL Server Management Studio\11.0\SqlStudio.bin for SSMS 2012 worked for me.

[Kyle Heon]

Worked for me with SQL 2008 R2. Just be sure you have SQL Studio closed before you do this or it recreates the file almost immediately.

[Ruben]

Make sure you close SQL Server Management Studio before deleting, or the file will be recreated with credentials EDIT: Like @KyleHeon said

[yzorg]

2014 users should use this! I am using SSMS 2014, I didn't have SqlStudio.bin (see top answer), but I followed the link in this answer and it worked (and is much easier).

[Simon Tewsi]

Note that despite the title of the link in this answer, "Remove cached login 2012", the answer it links to is about how to delete a cached server name, not a login. I didn't read that linked answer carefully enough and was trying the technique to remove a single login from the Login dropdown list. That doesn't work. It only works when you're removing a server name from the Server Name dropdown list. Along with deleting the server name it will also delete all cached logins for that server name; you can't delete just a single login and leave the others for that server.

[all2neat]

While I can't say it only impacts the login window, I can say this doesn't impact all settings across SSMS. I utilize the registered servers feature and after deleting this file those were still saved.

[Dr Manhattan]

Thank you very much, worked like a charm How did you figure out 1) That that file is a .NET binary serialization of the Microsoft.SqlServer.Management.UserSettings.SqlStudio class and 2) The reference to the namespace is located in the dll Microsoft.SqlServer.Management.UserSettings.dll and how you found its location

[Neil]

@DrManhattan If you binary serialize a very simple .NET class to file and open it in a text editor you will see a mix of binary data and text. Some of the text will be the values of your strings (if you have any in the class which was serialized). However the start of the file will be metadata about the root type which was serialized and the assembly it came from. Open your SqlStudio.bin file and you will see both ..UserSettings and ..UserSettings.SqlStudio. From there it was easy to find ..UserSettings.dll in the same directory as ssms.exe, which contained the namespace and class.

[Dr Manhattan]

That's awesome, thanks. I saw the metadata Microsoft.SqlServer.Management.UserSettings, Version=14.0.0.0, Culture=neutral..., you have taught me how to fish, thanks

[Muhammad Mamoor Khan]

I ran this code with the SSMS running and then checked there to see if it worked by restarting SSMS, and it didn't work, because the SqlStudio.bin was already loaded in memory by SSMS and then re-written by it before closing. Then I ran the code with SSMS closed and worked like a charm.

[Mr Lister]

Worked here too, so +1, but on my computer I didn't have the required DLL in the SQL Server directory. When I browsed I finally found it under the VS2017 directory where I would have expected it in the first place, except it wasn't in the Assemblies list in the Add Reference window. Oh well.

[patrickbadley]

This worked great in V18: "EDIT: You can simply press delete from the Server / User name drop down (confirmed to be working for SSMS v18.0). Original source from blog.sqlauthority.com/2013/04/17/… which mentioned that this feature is available since 2012!"

[Abdu Imam]

very nice solution. for me I'm using SQLServer 17. so I changed some paths. - for Microsoft.SqlServer.Management.UserSettings.dll i used this path "C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\" - for SqlStudio.bin i used this path ""C:\Users\%username%\AppData\Roaming\Microsoft\SQL Server Management Studio\14.0\"

[T.J. Crowder]

"You can simply press delete from the Server / User name drop down" I've just verified that that does not work in SSMS v17 and does work in SSMS v18. Pinal Dave's article says it works for the Server Name field, not the User Name field. As of v18, though, it definitely does work in the user name field.

[kooch]

Confirmed this procedure works as recently as v18.9.1.

[Константин Золин]

You need to close MS SQL Management Studio before edit UserSettings.xml, because It restore deleted login on closing.

[Dai]

@T.J.Crowder I'm using SSMS 18.10 with a saved registration with 2 saved logins (Me: with password, NotMe without a saved password). I want to remove the NotMe login/userid). Selecting NotMe and pressing <kbd>Del</kbd> does not correctly remove the NotMe option, while it does seem to remove it from the list initially, it's still in the drop-down list, and immediately after removing it the Save button is disabled - argh.

[Masoud Sadeghi]

I am using SSMS v18.4, and this solution worked for me. open Connect to Server > open Server name dropdown list and delete all saved logins.