Removing the remembered login and password list in SQL Server Management Studio
Sql ServerSecurityLoginSsmsSql Server Problem Overview
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?
Sql Server Solutions
Solution 1 - Sql Server
Another answer here also mentions since 2012 you can remove Remove cached login via https://stackoverflow.com/questions/10993861/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 - Sql Server
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 - Sql Server
For those looking for the SSMS 2012 solution... see this answer:
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 - Sql Server
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 - Sql Server
There is a really simple way to do this using a more recent version of SQL Server Management Studio (I'm using 18.4)
- Open the "Connect to Server" dialog
- Click the "Server Name" dropdown so it opens
- Press the down arrow on your keyboard to highlight a server name
- Press delete on your keyboard
Login gone! No messing around with dlls or bin files.
Solution 6 - Sql Server
As gluecks pointed out, no more SqlStudio.bin
in Microsoft SQL Server Management Studio 18. I also found this UserSettings.xml
in C:\Users\userName\AppData\Roaming\Microsoft\SQL Server Management Studio\18.0
. But removing the <Element>
containing the credential seems not working, it comes right back on the xml file, if I close and re-open it again.
Turns out, you need to close the SQL Server Management Studio first, then edit the UserSettings.xml
file in your favorite editor, e.g. Visual Studio Code. I guess it's cached somewhere in SSMS besides this xml file?! And it's not on Control Panel\All Control Panel Items\Credential Manager\Windows Credentials
.
Solution 7 - Sql Server
Delete entire node "Element" (inside "Connections" tree) from XML file, used by version 18 or higher.
C:\Users\%username%\AppData\Roaming\Microsoft\SQL Server Management Studio\18.0\UserSettings.xml
Solution 8 - Sql Server
For SQL Server Management Studio 2008
-
You need to go C:\Documents and Settings%username%\Application Data\Microsoft\Microsoft SQL Server\100\Tools\Shell
-
Delete SqlStudio.bin
Solution 9 - Sql Server
Delete:
C:\Documents and Settings%Your Username%\Application Data\Microsoft\Microsoft SQL Server\90\Tools\Shell\mru.dat"
Solution 10 - Sql Server
In XP, the .mru.dat file is in C:\Documents and Settings\Name\Application Data\Microsoft\Microsoft SQL Server\90\Tools\ShellSEM
However, removing it won't do anything.
To remove the list in XP, cut the sqlstudio bin file from C:\Documents and Settings\Name\Application Data\Microsoft\Microsoft SQL Server\100\Tools\Shell and paste it on your desktop.
Try SQL
If it has worked, then delete the sqlstudio bin file from desktop.
Easy :)