13

We migrated our servers from 2005 to 2014, User is able to delete it in the 2005 server But in 2014 we are getting the error as do not have permission or does not exits We have given to same permissions and even he is a system admin.In our application side they are not able to drop the view the application users are connecting through a user in our database he has all the permissions and he is sysyadmin

surya teja
  • 143
  • 1
  • 2
  • 9
  • 1
    Does the view actually exist? – Sean Lange Apr 07 '16 at 16:47
  • even I dropped the user and recreated it.but still the same..are there any differences in permissions in 2005 and 2014 – surya teja Apr 07 '16 at 16:51
  • 2
    What schema is the view in? What is the actual statement you are running? – Sean Lange Apr 07 '16 at 16:52
  • we have used username as the schema name. we are just joining the 2 table where user id ='xxx' – surya teja Apr 07 '16 at 16:55
  • If the view is there copy and paste the name in the DROP command. DROP VIEW XXX – Wes Palmer Apr 07 '16 at 18:08
  • I am able to drop the view but at the application side they are not able to drop the view.the application users are connecting through a user in our database he has all the permissions and he is sysyadmin – surya teja Apr 07 '16 at 18:16
  • If the view is not in the user's default schema (typically dbo) then you MUST specify the schema when trying to drop it. If the view is in schema xxx then you would have to run "drop view xxx.ViewName". If you simply said "drop view ViewName" it will not find it. – Sean Lange Apr 07 '16 at 18:25
  • Sean Lange you are correct but the thing is the application user is accessing the application through an user he has owned a schema xxx and his default schema is dbo. do you want me to change the default schema as xxx. – surya teja Apr 07 '16 at 18:48

10 Answers10

3

This problem can be simply solved if its simply happening, follow the steps:

  1. See which database you are working in, e.g I am working in the database student_fast.
  2. After getting the database name you are working in, write a query (use database_name) and execute it.
  3. Now try drop command.
  4. It works as per my problem.
סטנלי גרונן
  • 2,917
  • 23
  • 46
  • 68
M Anas
  • 41
  • 1
  • 5
0

I had a very similar problem. Make sure that the Schema didn't change when you moved the database.

In my case all the tables and views were placed in a schema with the databasename, so instead of the tables be called eg. table1, they were called database.table1.

As said, this is one reason this error is displayed.

Edit: after seeing the comments they suggest the same thing, to use the schema before the tablename, and yes. if dbo is your schema, then try writing dbo.tablename

Sourcery
  • 641
  • 1
  • 5
  • 15
0

I've faced the same problem (SQL Server 2016).

It is a little bit strange, but when I connect to SQL Server via Windows Authentication using my local Windows Account (that is also mapped to sa) I get this error.

When I connect to SQL Server via SQL Server Authentication using username sa and its associated password, I have all the permissions and this nasty error disappears.

AlexMelw
  • 2,406
  • 26
  • 35
0

If an application/any other process is already connected to the database you are trying to drop , you may get that error. Try to disconnect from the database first and drop the schema , it should work.

mykey
  • 1,943
  • 19
  • 13
0

Restarting the SQL server worked for me. But it's not an affordable solution for production environments...

Nir
  • 1,836
  • 23
  • 26
0

Error :- "Cannot drop the table 'ProductDb', because it does not exist or you do not have permission." even I was facing the same error..... then I created the same table with same name and same attributes. then update the database or refresh the database it solved my error. This error occurred when you delete the table directly from the database.

Pawan Kumar
  • 1
  • 1
  • As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Apr 28 '22 at 12:49
0

If tables are not exist in database, then we can remove drop command from migration code. Since it looking for table in database and it's not exist hence it's giving error.

Ibrahim Payak
  • 1
0

Instead of Modifying the Migrations code ,we could simply delete the database in the sql-server and do add-migration followed by update-databse -verbose which will create those tables in the database with all intact previous tables and data.

Anisa khanum
  • 1
  • As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Mar 31 '23 at 00:08
-1

In your case check if you make Drop view Statement twice , sometimes you delete the view in two different places , this will cause to this problem .

Sameeh Shahin
  • 1
-2

It has been years since it is posted. I run across the same situation. If I run as followed:

Drop view [Schemaname.viewname]

It will have the error.

If I take away the [ ], as 

Drop view Schemaname.viewname

It works. Not sure what's the reason.

RN92
  • 1,380
  • 1
  • 13
  • 32
Joe
  • 1