CODEWITHSUNDEEP
sql-serverstored-procedurespermissions
Twiggz
Twiggz

Reputation:

Cannot find the object "XXX" because it does not exist or you do not have permission

I'm getting the following error when calling a stored procedure:

Cannot find the object "XXX" because it does not exist or you do not have permission.

I've checked the database and the SP is there with the correct permissions yet I'm still getting the error.

Any suggestions?

Upvotes: 16

Views: 89462

Answers (13)

Henry.K
Henry.K

Reputation: 377

If in your stored procedure there are the SET IDENTITY ON & OFF statements, so you need ddl_admin rights added to the user login. This was my solution.

Upvotes: 4

Had To Ask
Had To Ask

Reputation: 79

Possible typo or already removed? Refresh your view or query for it (looks like this was for a stored procedure):

SELECT name AS procedure_name   
    ,SCHEMA_NAME(schema_id) AS schema_name  
    ,type_desc  
    ,create_date  
    ,modify_date  
FROM sys.procedures;

Then if it's listed, copy the name and try your query with the name taken from those results (or copy the object's name any other way you may be familiar with).

MORE GENERIC SOLUTION FOR ANY OBJECT (if you have SSMS): In SQL Server Management Studio, right-click on the object, "Script [OBJECT_TYPE] as", DROP To (or the action you're attempting), New Query Editor.

If you can run this successfully you probably had a typo.

Upvotes: 0

Erkan günerhan
Erkan günerhan

Reputation: 1

The scheme may be different. If there is a different scheme, add it in front of it.

Upvotes: 0

dgcharitha
dgcharitha

Reputation: 345

Even after adding 'GO' issue did not fixed for me. Then removed UPDATE STATISTICS [Table_name] statement Then my issue was fixed.

Upvotes: 0

Aaron Bertrand
Aaron Bertrand

Reputation: 280439

Always use the dbo. (or other schema) prefix both when creating and when accessing objects.

I wrote about this very topic recently:

Upvotes: 0

Chris Halcrow
Chris Halcrow

Reputation: 31970

In Microsoft SQL server, selected the object in the object explorer that you want to work with, right-clicking it, then doing 'Script [object] as' can give you the script you need to perform an operation successfully without getting this error

Upvotes: 0

SRA
SRA

Reputation: 1

Using the 'GO' fixed the issue for me as well. This was driving me crazy, after multiple drops and checking permissions for users and schema, this is what finally helped.

Upvotes: 0

raz0r89
raz0r89

Reputation: 51

I meet this issue too. In my case, I granted execution permission just after creating stored procedure. And there is no 'GO' between the two statements. I added GO, and it works.

Upvotes: 4

Shane
Shane

Reputation: 141

I discovered that I had left off the "GO" word after "END" in my stored proc. Altering the Proc and adding back GO fixed this issue for me.

Upvotes: 14

gaijintendo
gaijintendo

Reputation: 423

Similar to marked answer: The final line of my stored procedure was a line which granted permission to the stored procedure to run as the appropriate user - probably added there when I generated a script.

Removing that (or perhaps a hidden character attached) managed to fix it.

"XXX" was the name of the stored procedure I called, successfully (it made the desired change) but which gave me this error.

Upvotes: 3

Twiggz
Twiggz

Reputation:

Ok, here's what happened. There was a special character before the end of the SP so it was incomplete yet still valid, somehow.

So I could see the SP and see the permissions on it but I could not run it. So to solve the issue I had to copy the text out of SQL Management Studio and paste it into Notepad, then remove the special character, then copy and paste it back into SQL Management Studio and run the alter script.

Very strange how the character got there!

Upvotes: 4

gbn
gbn

Reputation: 432471

As well as other answers about schema/security etc:

  • do you have a DENY on it somewhere?
  • case sensitive object names and using "wrong" name?
  • wrong database context? eg OtherDB.dbo.Myproc

Upvotes: 0

Andrew Hare
Andrew Hare

Reputation: 351586

The account that you are using when calling the stored procedure must not be the same account that you are using to check it. Make sure that the account that you are using to execute the sproc has access to the object.

Upvotes: 1

Related Questions