Can't publish data from one SQL feature dataset - how to diagnose dataset corruption/problem

[AmyBrassieur]

I have one SQL Server 2017 feature dataset where I can use the data in maps and edit it, but the publish operation fails after packaging. Previously created services using this data suddenly stopped working between a Friday and Monday. Esri Tech Support helped me troubleshoot permissions, etc, and isolate the issue to my most often used feature dataset. Other SQL data outside that feature dataset publish just fine. 

I tried exporting the data to file geodatabases, then creating a new feature dataset and importing the feature classes into that. This had same issue of publishing. 

I also tried importing the data that was exported to file geodatabases back to the database but not inside a feature dataset, same issue of publishing. 

My only SQL Server experience has been installing it and upgrading from 2008 R2 to 2017. I don't know my way around SQL Server.

I'm looking for someone with more SQL Server knowledge to tell me if there's anything I can do to make this data behave again, tell me what the operation is called, point me in the right direction.  Thanks!

[George_Thompson]

What is the DB authentication being used?

Was there an update of any passwords?

What changes happened during that time frame?

[AmyBrassieur]

It's operating system authentication, no update of passwords, no changes discovered. Windows Update History shows nothing for the time period. I'm the only person with access. 

[George_Thompson]

Thanks for that information. The fact that you can reload the data and still not publish is pointing towards data issues.

Is the data versioned?

What is the specific error with packaging that occurred?

Was the data published as referenced or copied to server?

Who owns the data?

This one may be tough to troubleshoot via the forums. I do have some SQL that you can use to collect all the permissions of all the tables in the Enterprise Geodatabase. You could compare permissions on tables that work to ones that do not.

[AmyBrassieur]

You're right about this being tough to troubleshoot. The data was published as referenced, the data is versioned, I don't have the error in front of me but it basically said it couldn't see the data. I owned the data, there is no one besides me in the database. It's a particular feature dataset giving problems, it's the one I use the most. It's acting a little bit corrupt. It works to edit the data in ArcMap, exporting the data is fine, simply can't publish from it or make the previously published services see the data. 

I don't remember doing anything with the database in the time it stopped working, don't understand how it's a little corrupt, and clueless about what to do to fix it. 

This feature dataset is a "set it and forget it" thing, it's been humming along for years till it didn't.