0

So have a weird situation - a user on one machine can edit table columns (add or delete) without issue, but move to a newer machine and attempts to edit a table's columns results in an error message: "saving changes is not permitted"

Both SSMS are 18.12.1 and both have "Prevent saving changes that required table re-creation" enabled. This only happens on the new machines, all DBs work fine on the old machines and edits can be made with no issues.

To be clear, both installs of SSMS are completely default with no changes made and the same users test. Even SA accounts cannot make table edits on the new machines but can on the old machines fine.

Is there some SSMS setting that needs flipped or allow changes from another location?

This is NOT a duplicate of Sql Server 'Saving changes is not permitted' error ► Prevent saving changes that require table re-creation

Deadder
  • 535
  • 1
  • 4
  • 16
  • 1
    _"both have "Prevent saving changes that required table re-creation" enabled"_ - why do you think this is not the cause of the error message? – Dai Nov 21 '22 at 20:00
  • _"Even SA accounts cannot make table edits on the new machines but can on the old machines fine."_ - **it depends on what the changes are** and you haven't told us what those are or that the same user tried the _exact same_ changes on all machines - some changes don't require a table-copy (e.g. adding new nullable columns to the end of a table, and changing certain columns' column-types, but others (such as inserting a new column before other columns, or reordering columns) do. – Dai Nov 21 '22 at 20:02
  • 1
    What is it you are trying to actually do? What action are you taking that would require the table to be recreated? Why not use T-SQL to do the task you need to do, rather than the quirky/flawed designer? – Thom A Nov 21 '22 at 20:05
  • Also, you don't explain *why* this isn't a duplicate when it very much appears to be. – Thom A Nov 21 '22 at 20:06
  • @Dai Edited the question to clarify that it is adding/deleting columns. – Deadder Nov 21 '22 at 20:11
  • Adding and removing columns does not require recreating the whole table. – Thom A Nov 21 '22 at 20:14
  • @Larnu The reason it isn't a duplicate is because the solution for that question states that to "fix" this is to disable "Prevent saving changes that required table re-creation". However, the option is enabled on the working machines as well as the non-working ones. So if that is the issue - then why doesn't it affect all machines the same? – Deadder Nov 21 '22 at 20:14
  • *"Is there some SSMS setting that needs flipped or allow changes from another location?"* The question you say this is *not" a duplicate of tells you what that setting is, so wjy doesn't that question answer the question? – Thom A Nov 21 '22 at 20:15
  • 1
    Because you are likely doing *different things*. Why not just write the T-SQL as I asked? Why not just `ALTER TABLE dbo.YourTable ADD NewColumnName varchar(20) NULL;`? – Thom A Nov 21 '22 at 20:16
  • 1
    @Larnu _"Adding and removing columns does not require recreating the whole table"_ - Adding a `NOT NULL` column with a `DEFAULT` to the middle of a table does. – Dai Nov 21 '22 at 20:17
  • 1
    Yes, but the ordinal position of a column *shouldn't* matter, @Dai . If it does the flaw is in the code referencing the object. As for having a new column with a `DEFAULT` value, that *doesn't* need a recreate. – Thom A Nov 21 '22 at 20:28
  • @Dai and Larnu: Ahh - so SSMS is doing different things behind the scenes despite it looking the same to user. So the "issue" here is that SSMS is attempting to create a new column in the middle of the table on the machines with the error? Whereas it is appending to the end of the table on the machines it works for...TSQL works correctly. Now...the weird thing is after adding the column via ALTER the designer works on the table fine...issue disappears. – Deadder Nov 21 '22 at 20:29
  • No, the user is doing different things, not SSMS. – Thom A Nov 21 '22 at 20:30
  • 2
    @Deadder Use the "Show change script" button in future. I'm not a fan of the Table Designer ever since the SQL it generated caused me to lose a production table because it never checked that the rebuild was successful before running `DROP TABLE`, grrrr. (Also it doesn't support Temporal Tables either) and it generates change-script code that's completely different to the rest of SQL Server's tooling (both elsewhere in SSMS and SSDT too) - it feels like MS has abandoned it. It does have the feel of a component ripped from _Enterprise Manager 2000_... – Dai Nov 21 '22 at 20:30
  • @Dai - that explains it. I will relay this info to the various users and DBAs this affects. To be honest, I would rather them use TSQL anyways, but old habits die hard and has been a struggle to get them to move - now we have justification to force the use of TSQL and not the designer. – Deadder Nov 21 '22 at 20:39
  • @Deadder But you shouldn't be editing DBs _live_ anyway - why aren't you using SSDT? – Dai Nov 21 '22 at 20:41
  • @Dai This was an issue on several test and development DBs that will have various scripts and statements run against them for a variety of reasons. Ultimately, prod data is more locked down and we prevent untracked changes. – Deadder Nov 21 '22 at 20:49

1 Answers1

0

Big thanks to @Dai and @Larnu for helping to figure out what was happening.

TLDR; Use TSQL for table structure changes. Don't use SSMS table designer.

This ultimately was something odd with the designer in SSMS and the code it generates not being consistent. In our instance, we were able to find an instance where the same designer change on two separate machines were outputting different ALTER code despite the change (adding a column) being the same. The oddities continue as after the correct ALTER statement is run, the designer in SSMS start working correctly, and consistently.

I suspect there may be more user variable actions involved in this as suggested by @Larnu but, with the use of TSQL as standard instead of the designer, we can just avoid the issue altogether.

Deadder
  • 535
  • 1
  • 4
  • 16
  • 2
    As I mentioned, this likely is t a lack of consistency of the designer here, it's flawed but is consistent on those flaws. The inconsistency is in the actions of the users. For example, perhaps one was adding the column at *the end* of the table while the other was not; the latter *cannot* be done without a new table being created. This is just one, of many, reasons why using T-SQL is just "better", as you write the script once and then if you need to run it again you just use the same script. – Thom A Nov 21 '22 at 20:56
  • The designers have been consistently broken for, oh, ever, and I have been recommending against their use [for 13 years or more](https://sqlblog.org/2009/10/14/bad-habits-to-kick-using-the-visual-designers). – Aaron Bertrand Nov 21 '22 at 21:52