Search code examples
sql-serverssms

False Error - Cannot find the object "TABLE" because it does not exist or you do not have permissions

I have been getting this error using the SSMS 2019 (19.0.1) table designer:

Cannot find the object "xxxxx" because it does not exist or you do not have permissions.

In the past, I have done this with other tables and it works fine. I clearly have permission, because to get around the error, I had to script out the data, drop the table, and recreate it and reload the data. The table clearly exists, because I'm using the SSMS Designer to change it.

If I add a column to the bottom of the table, I don't get this error. Only when adding between two existing columns at the "top" of the column list.

Example (we are adding an environment variable which is really more important than the other variables):

enter image description here

I know people say order of the columns doesn't matter, but in our opinion (my company, colleagues and mine), the order is important. So often we do "right click, then "Select Top 1000 Rows" to view the table. And yes, I know we can create views, but that's not what we want.

Towards the end of the table, we have all the security fields, like CreatedBy, ModifiedBy, CreatedDate, ModifiedDate, etc... We don't real user data fields after the columns. We want the most relevant data viewable when doing a default query.

I have read other posts that talk about how you cannot use "Alter" to add a new column between two existing columns, but often times the Designer will do it for you.

So my question is, why does the designer do it perfectly fine on some tables, and why does it give the above misleading error on other tables? By the way, we use AutoAudit (https://github.com/koenmd/AutoAudit/blob/master/AutoAudit%203.30a.sql) on these tables, which creates triggers and views that track who changed what. Could it be that having views or triggers on the table cause this situation?

Solution

  • The Dan Guzman comment above that helped was to Generate the Script from the Designer, then run it separately. This is done as shown below (add your column, right click, select "Generate Change Script":

    enter image description here

    When I did that, I noticed the issue is with the AutoAudit triggers or related views.

    Dropping AutoAudit components from table: [dbo].[My_TableName]
Dropping Table Audit DDL
Msg 4902, Level 16, State 1, Line 67
Cannot find the object "dbo.My_TableName" because it does not exist or you do not have permissions.
Msg 15248, Level 11, State 1, Procedure sp_rename, Line 419 [Batch Start Line 68]
Either the parameter @OBJName is ambiguous or the claimed @objtype (OBJECT) is wrong.
Msg 1779, Level 16, State 0, Line 71
Table 'My_TableName' already has a primary key defined on it.
Msg 1750, Level 16, State 0, Line 71
Could not create constraint or index. See previous errors.
Msg 2714, Level 16, State 2, Procedure My_TableName_Audit_Insert, Line 1 [Batch Start Line 77]
There is already an object named 'My_TableName_Audit_Insert' in the database.
Msg 2714, Level 16, State 2, Procedure My_TableName_Audit_Update, Line 1 [Batch Start Line 231]
There is already an object named 'My_TableName_Audit_Update' in the database.
Msg 2714, Level 16, State 2, Procedure My_TableName_Audit_Delete, Line 1 [Batch Start Line 543]
There is already an object named 'My_TableName_Audit_Delete' in the database.
Msg 2714, Level 16, State 2, Procedure My_TableName_trgAfterUpdate, Line 1 [Batch Start Line 682]
There is already an object named 'My_TableName_trgAfterUpdate' in the database.
Msg 3902, Level 16, State 1, Line 697
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
Msg 2714, Level 16, State 5, Line 700
There is already an object named 'FK_My_TableNameCh_BizTalk_Email_Event' in the database.
Msg 1750, Level 16, State 1, Line 700
Could not create constraint or index. See previous errors.
Msg 3902, Level 16, State 1, Line 713
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

    I figured out how to remove AutoAudit using

    exec [Audit].[pAutoAuditDrop] ...

    Strangely enough, after that, the designer still gave the same error. I generated the script again, ran the script, and it ran and did the change. So for now I'm happy with the results, but seems like something buggy with the designer tool.