@DavidBrowne-Microsoft Hi David, it returns dbo

@Ronaldo no other users have high enough privilege. And yes it seems that create or alter table throws the error. I attempted to create another login with the necessary privileges and had the same issue.

@MBuschi the database is not read only, thanks for the suggestion

@MaxB Can you please post the exact and complete error message(s)? It's possible that you are getting an error from something else, like a DDL trigger, and not from the CREATE TABLE statement itself. Also, can you please post the exact CREATE statement that you are trying to execute? It's possible that this is just a wild goose chase at the moment.

@SolomonRutzky I can not provide the exact create statement unfortunately... company policy and all.. Question updated with error message

@MaxB Thanks for providing more info. However, that's still not the complete error. It's only the second part. What shows in SSMS? No screen shot is needed (even the error log image can be removed), just the full text from what appears to be error 297. As for the create statement, I understand about company policy, but it's fine to redact/change column names so that you aren't really giving anything away.

@SolomonRutzky that error message just says Error: 297, Severity: 19, State: -1.. Will update question with the query

What edition of SQL Server? Standard, Enterprise, Express, Express LocalDB? And are you actually in [tempdb] when executing the CREATE TABLE, or what DB are you in?

@SolomonRutzky Sql Server 2017 Standard. The DB I am in when executing the query is my db that I wish to alter. Am able to run this query on several other identical instances of this exact database just fine. Just one local instance giving me the headache.

@MaxB Ok, I assume that the error happens with the CREATE statement? If so, can you do: CREATE TABLE dbo.[_test] ([Col1] INT);? In either case, do any other tables currently exist in this DB? If so, are you able to add any rows to any of them?

@SolomonRutzky I am unable to create a table using that query. I am able to edit tables using SSMS and also add rows.

@MaxB Can you create a View and/or Function, anything? Do you have a system policy that cancels CREATE TABLE statements?

@SolomonRutzky just tested this and am unable to create anything. I get the same error.

@MaxB 1) Can you create a temp table in that problem database? CREATE TABLE #Bob (Col1 INT);? and 2) can you create tables in other DBs on that problem instance? USE [tempdb]; CREATE TABLE dbo.Bob2 (Col1 INT);?

@SolomonRutzky I can not create any tables in the problem database, but I can create a new database and populate it.

Thank you for offering so much assistance

@MaxB I understand that you cannot create regular tables in the problem DB, but temp tables exist in [tempdb]. Please try: USE [problem_db]; CREATE TABLE #Bob (Col1 INT); and USE [tempdb]; CREATE TABLE dbo.Bob2 (Col1 INT); and report the result of each. Thanks.

@MaxB You're welcome :-). I just updated the query at the bottom of my answer related to trigger stats. Please re-execute with the updated code as it will show the Trigger name and its definition.

USE [problem_db]; CREATE TABLE #Bob (Col1 INT); and USE [tempdb]; CREATE TABLE dbo.Bob2 (Col1 INT); both worked just fine for me actuall

actually

The query you provided for trigger stats does not return anything for each one. All empty

@MaxB Try creating a real table in the problem DB again (e.g. CREATE TABLE dbo.[_test] ([Col1] INT); ), and then, assuming it fails, execute the trigger stats query while in the problem DB.

@SolomonRutzky just gave this a try. Permission denied on creating a real table, trigger stats query yields no results

@MaxB Hmm.. What compatibility mode is the problem DB in?

Sql Server 2014 (120)

I have also tried it in 2017 (140)

Ok. Execute this in the problem DB: CREATE SCHEMA [_DeleteMe];

That executes

@MaxB Interesting. Now try: CREATE TABLE [_DeleteMe].[_test] ([Col1] INT);

The user does not have permissions :(

@MaxB And you have no sys policies and/or DB / Server audits running?

Nope, I even restored this database from another database that is identical on another machine.

Other machine no problems, this machine I have problems

Ok, please try: DBCC TRACESTATUS(-1);

No error messages returned

@MaxB 0 rows returned from DBCC?

Try this while in the problem DB and compare to other instance (while in the same DB). Are any values different? If not, uncomment the second line, execute on both instances and compare all columns, report any differences:

Odd. The query is 4 lines long. Did copy/paste not work correctly? I originally had single back quotes at the beginning and ending to hopefully format it as code but that didn't work so I removed those..

Ah got it

for "is_trustworth_on" it says "0" for the login I am using

@MaxB Not sure what you mean by "for the login I am using" as that is a database setting, not per login or user..

Sorry I misinterpreted the line of information

But looking at the problem db compared to a working db there is no difference in what the query returns

@MaxB exact same owner (SID and name)? and same trustworthy setting?

Sorry owner SID is very different

name is the same, trustworthy setting is the same

@MaxB are the owners Windows logins or SQL Server logins?

SQL Server logins

wait one second

yes all sql server logins

@MaxB Then you likely need to sync the dbo owner SID in the restored (i.e. problem) DB with the local logins SID, but first we should check the DB itself using:

wait a minute

Sorry the SID values are a match between problem database and database it backed up from

Nevermind disregard that last message I am confusing myself right now

@MaxB either way, the SID in the DB for "dbo" needs to match the owner_sid reported by sys.databases

yes

I understand that now

And the SID returned by your query does not match the owner sid

@MaxB does it match on the system that works?

Believe it or not no it does not match on the working system

But checking other databases that are the same on other machines they do match

OK the problem sid's have been synced. Attempted to create table on problem_db but issue still remains

@MaxB Ok, now it's time to uncomment the , * (i.e. line 2) in my sys.databases query and compare between working and non-working DBs..

It is the same

@MaxB just to confirm, all DB properties are the same between working and non-working DB?

@MaxB Also, can you install a new instance of SQL Server 2017 on a different computer (it can even be 2017 Express) and restore the DB to that instance and see if it works?

Sorry for the late reply, yes they are

Would it be worth it to clone the bad database on to my machine as a different db and test?

@MaxB Yes, I was considering asking you to restore the problem DB to the same instance as a different DB name (be sure to change folders, etc for restored data and log files)....

Also, do you by any chance have resource governor running? and please use the "reply" link on the left side of the message so it sends a notification :)

@SolomonRutzky I will do the restore now and see what happens. I do not have any resource governor running

@SolomonRutzky doing the restore as a different name does not help unfortunately.

@MaxB Interesting... You restored into the same instance, right, hence the need for a different name? Can you try my other suggestion of restoring into a different instance, even SQL Server Express 2017 running on your local machine... you said the DB was only 5 GB so that should work given the 10 GB max for Express Edition..

@SolomonRutzky System.Data.SqlClient.SqlError: RESTORE detected an error on page (27359:1042085544) in database "Localdb_BAD" as read from the backup set. (Microsoft.SqlServer.SmoExtended

This is what I am told when trying to restore the bad database to my instance

@MaxB perhaps we found the problem ;-) what exact DBCC CHECKDB statement did you run to analyze the problem DB? perhaps a different setting would have detected this?

Also, can you take a copy only backup of the working DB on the good instance and restore that as a different name to the instance with the bad DB?

@SolomonRutzky I only ran "DBCC CHECKDB"

@SolomonRutzky attempting to restore a good database on the bad instance gives me the following

System.Data.SqlClient.SqlError: RESTORE detected an error on page (27359:1042085544) in database "Localdb_good" as read from the backup set. (Microsoft.SqlServer.SmoExtended)

@MaxB You might want to try the following on both good and bad DBs: DBCC CHECKDB(N'problem_db') WITH EXTENDED_LOGICAL_CHECKS;

@SolomonRutzky CHECKDB found 0 allocation errors and 0 consistency errors in database is the response I get from both db's

@MaxB well, that's less than helpful. I think we have stumbled upon something significant. Though maybe you are using a feature in Standard Edition that isn't available in Express, but I don't think it would result in that "error on page" message. Besides, was this actually a brand new copy only backup, or an existing backup? And are you restoring into Standard or Express Edition?

In either case, I am for the moment out of both time and ideas...