SQL Server Management Studio and the "Saving changes is not permitted" error.

Sometimes after a new install of Microsoft SQL Server Management Studio, you may get a default setting that prevents the user from changing the design of tables in the visual designer mode, which can be extremely frustrating, as it is not easy to figure out how to turn the checking off that is preventing table redesign. The error message will be announced in an annoying popup whose text is uncopyable:

"Saving changes is not permitted. The changes you have made require the folloing tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created."

Here's how to solve this in Microsoft SQL Server Management Studio 2008 through 2014:

1) Go into the Tools...Options... menu

2) In the popup, on the left, expand "Designers" by clicking on the plus

3) In the "Table Options" shown to the right, MAKE SURE that "Prevent saving changes that require table re-creation" is NOT checked.

I've lost a few hours of my life to this nuisance. Hope this will help someone else out of the conundrum.

ODBC workaround (Access to SQL Server) for 64-bit Windows 7

I've been avoiding 64-bit Windows due to various incompatibility rumors, but this case takes the cake, as it is entirely Microsoft's fault. My work place uses a variety of shared Access databases, located on a network drive, that connect via ODBC System DSN's to a SQL Server 2008.

Even though all DSN's appeared to be configured correctly on my colleague's brand new (64-bit) Windows 7 machine, and the ODBC connections passed their test, the actual database declined to connect to the server. Thanks to various discussion groups, we finally figured out that the graphical user interface accessible from the Administrative Tools applet in Control Panel actually brings up a 64-bit ODBC application, whereas we (for backwards compatibility) needed the strangely hidden 32-bit System DSN window. To run it, we had to browse to this path:

C:\Windows\SysWOW64\odbcad32.exe

Clicking on odbcad32.exe runs the 32-bit version of the ODBC connection setter upper. There, we re-created all the System DSN's, and finally the Access databases were happy.



By default, the Windows GUI is presenting a 64-bit ODBC connection setter upper (which I believe is in the C:\Windows\system32 path somewhere. Going manually to the first path and running the application, then adding the ODBC connections, makes it work.

In the meantime, 3 days of work were lost to this problem.

Sql Server Management Studio 2008 logon persistence problem

I very often open SQL Management Studio 2008 for the same server. Problem is, the very first time I accessed this server, I did so using a different user name and password. Forever after henceforth, SQL Management Studio refused to remove this old server entry from my dropdown list in the "Connect to Server" popup window.

This made it necessary to click to change the user and enter a new password about 80 times per day--all because this premium, allegedly very smart sophisticated software has decided never to forget a former entry--even though I have deleted the Registered Server and recreated and so forth. I'm frankly angry about this nuisance, which wouldn't matter if I didn't have to open and close the tool so many times per day.

If anyone has any concrete suggestion on how I can defeat this thing, please let me know. I see from the internet that I am not the only one having this frustration, but I have yet to find any blog entry or suggestion to alleviate the problem.

UPDATED with an answer in Aug. 2010:

For Sql Server Management Studio 2008 on Windows XP, to restart all your logins, delete the file:

C:\Documents and Settings\%username%\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell\SqlStudio.bin

or possibly:

C:\Documents and Settings\[user]\Application Data\Microsoft\Microsoft SQL Server\100\Tools\Shell

----

For Sql Server Management Studio 2008 on Windows 7, to restart all your logins, delete the file:

C:\Users\%username%\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell\SqlStudio.bin

Delete a maintenance plan after changing SQL Server 2008 name

I've seen a few posts on how to delete an older maintenance plan after you've changed the computer name for a default installation of SQL Server 2008, but none of the posts fully solved the problem. Below is what I had to do--with the caveat that you have to find your own id's:

USE msdb

DELETE 
FROM dbo.sysmaintplan_log 
WHERE subplan_id = '36F8247F-6A1E-427A-AB7D-2F6D972E32C1'

DELETE 
FROM dbo.sysmaintplan_subplans 
WHERE subplan_id = '36F8247F-6A1E-427A-AB7D-2F6D972E32C1'

DELETE 
FROM dbo.sysjobs 
WHERE job_id = '3757937A-02DB-47A6-90DA-A64AE84D6E98'

DELETE 
FROM dbo.sysmaintplan_plans 
WHERE id = 'C7C6EFAA-DA4D-4097-9F9F-FC3A7C0AF2DB'