Error when running "find invalid objects"

[georhill]

I get the following error when I right-click on some of our databases (SQL Server 2008 R2 64-bit) and select "find invalid objects" (SQL Prompt 5.3.2.2).

"You have insufficient permissions to find invalid objects in EbusPPROD.
Your user must have the VIEW DEFINITION permission granted on the database to find invalid objects."

The database log in I am using has sysadmin role so I question that it is a permissions issue as stated in the error message.

For the database above, SQL Prompt says it is checking 1000 objects. Is that too many? It's not a huge database, at least size-wise.

A couple of other databases in the same instance have 5 objects and 38 objects and "find invalid objects" completes successfully and says it did not find any invalid objects.

Thanks for any input.

[Brian Donahue]

Thanks for your question - I have been looking into a similar situation with Prompt earlier in the week - Prompt checks for invalid objects using the execution plan for every object, so it's unlikely you will see a match between select count(*) from sysobjects and the number of objects reported by Prompt.

It also has the capability to go through linked servers, so it is possible that Prompt is trying to check an object on the other side of a linked server.

Hopefully this helps.
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521