Cannot find the object because it does not exist or you do not have permissions Error in SQL Server

Question

I have a database and have a Sql script to add some fields to a table called  Products  in the database   But when i am executing this script  I am getting the following error   Cannot find the object  Products  because it does not exist or you do not have permissions   Why is the error occurring and what should I do to resolve it

User · Answer

Does the user you re executing this script under even see that table    select top 1   from products   Do you get any output for this    If yes  does this user have the permission to modify the table  i e  execute DDL scripts like ALTER TABLE etc   Typically  regular users don t have this elevated permissions

User · Answer

This could a permission issue  The user needs at least ALTER permission to truncate a table  Another option is to call DELETE FROM instead of TRUNCATE TABLE  but this operation is slower because it writes to the Log file  whereas TRUNCATE does not write to the log file      The minimum permission required is ALTER on table name  TRUNCATE TABLE   permissions default to the table owner  members of the sysadmin fixed   server role  and the db owner and db ddladmin fixed database roles    and are not transferable  However  you can incorporate the TRUNCATE   TABLE statement within a module  such as a stored procedure  and grant   appropriate permissions to the module using the EXECUTE AS clause

User · Answer

The TRUNCATE statement was my first problem  glad to find the solution here   But I was using SSIS and trying to load data from another database  and it failed with the same error on any table that used IDENTITY to create an auto-incrementing ID   If I was scripting it myself I d first need to use the command SET IDENTITY INSERT tablename ON  and then  SET IDENTITY INSERT tablename OFF when the table update was done   But this requires ALTER permissions on the table  which I do not have   Hence the error message in SSIS on the table load  even though the previous step had just deleted all the data out of the table

User · Answer

I found a reason why this would happen   The user had the appropriate permissions  but the stored procedure included a TRUNCATE statement   TRUNCATE TableName   Since TRUNCATE deletes items without logging  you  apparently  need elevated permissions to execute a stored procedure that contains it   We changed the statement to   DELETE FROM TableName      and the error went away

User · Answer

Look for any DDL operation in the script  Maybe the user does not have access rights to run changes  In my case it was SET IDENTITY INSERT tblTableName ON You can either add db ddladmin for the whole database or for just the table to solve this issue  or change the script  -- give the non-ddladmin user INSERT SELECT as well as ALTER  GRANT ALTER  INSERT  SELECT ON dbo tblTableName TO user name

User · Answer

Are you sure that you are executing the script against the correct database  In SQL Server Management studio you can change the database you are running the query against in a drop-down box on one of the toolbars  or you can start your query with this   USE SomeDatabase

User · Answer

Sharing my case  hope that will help   In my situation inside MY PROJ Database- gt MY PROJ Database sqlproj I had to put this    lt Build Include  dbo Tables MyTableGeneratingScript sql    gt

User · Answer

It can also happen due to a typo in referencing a table such as  dbo Product  instead of  dbo   Product

User · Answer

I ve been trying to copy a table from PROD to DEV but get an error   Cannot find the object X because it does not exist or you do not have permissions    However  the table did exist  and I was running as sa so I did have permissions   The problem was actually with CONTRAINTS  I d renamed the table on DEV to be old XXX months ago  But when I tried to copy the original one over from PROD  the Defaut Constraint names clashed   The error message was misleading

User · Answer

It could also be possible that you have created the  Products  in your login schema and you were trying to execute the same in a different schema  probably dbo   Steps to resolve this issue  1 open the management studio  2  Locate the object in the explorer and identify the schema under which your object is    it is the text before your object name    In the image below its the  dbo  and my object name is action status    if you see it like  yourcompanydoamin yourloginid  then you should  you can modify the permission on that specific schema and not any other schema   you may refer to  Ownership and User-Schema Separation in SQL Server

User · Answer

In my case I was running under a different user than the one I was expecting   I had  DRIVER  SQL Server  SERVER     DATABASE     Trusted Connection false User Id XXX Password YYY  as the connection string I passed to pypyodbc connect    but the connection was still using the credentials of the Windows user that ran the script  I verified this using the SQL Server Profiler and by trying an invalid uid password combination - which didn t result in an expected error    I decided not to dig into this further  since switching to this better way of connecting fixed the issue   conn   pypyodbc connect driver   SQL Server    server  servername   database  dbname   uid  userName   pwd  Password

User · Answer

You can right click the procedure  choose properties and see which permissions are granted to your login ID   You can then manually check off the  Execute  and alter permission for the proc     Or to script this it would be   GRANT EXECUTE ON OBJECT  dbo  PROCNAME      TO  ServerInstance user    GRANT ALTER ON OBJECT  dbo  PROCNAME      TO  ServerInstance user

User · Answer

In my case the sql server version on my localhost is higher than that on the production server and hence some new variables were added to the generated script from the localhost  This caused errors in creating the table in the first place  Since the creation of the table failed  subsequent query on the  NON EXISITING  table also failed  Luckily  in among the long list of the sql errors  I found this  OPTIMIZE FOR SEQUENTIAL KEY   OFF  to be the new varialbe in the script causing my issue  I did a search and replace and the error went away  Hope it helps someone

[sql-server] Cannot find the object because it does not exist or you do not have permissions. Error in SQL Server

Examples related to sql-server