Find invalid objects - insufficient permissions?

basilisk

Hi all,

I found a very strange issue with the latest build of SQL Prompt (5.1.4.11) - when running a "Find invalid objects" operation on a medium-sized database it gets stuck roughly at 25% (46 out of 213 objects checked) for exactly 20 seconds, then it gives a "You have insufficient permissions to find invalid objects in DB_NAME" error message. The problem is that I'm logged on as a sysadmin. Is there a way to find out why it stops?

Thanks,
B

Paul Stephenson

Hi basilisk,

You can see what the Find Invalid Objects operation is doing by using the SQL Server Profiler. This should show you the object it's looking at when the pause happens.

Regards,
Paul

basilisk

I found the object in question, but it looks like it moves on right before crashing - the last statement in the profiler is
SELECT text,ctext,encrypted,number from [DB_NAME].dbo.syscomments c WHERE id=<blah>. Anyway, I was able to execute all of the statments from the profiler by hand just fine. Still looking...

B

mikef

Howdy,

I think I'm having the same issue...



I'm connecting to a sql2000 db via SSMS 2005 as a user who is dbo.owner.

It seems to hang at the "SELECT text,ctext,encrypted,number from [DB_NAME].dbo.syscomments c WHERE id=<blah>" point.

Running the query manually works, it pulls a "create" script for a SP.

Cheers,
Mike.

ps... your tools kick ass!

basilisk

Ok, I think I narrowed it down.
I get this error message when (and only when) the stored procedure has the exec master..xp_cmdshell command. There's no way around it in my setup, but at least it might help other people.

mikef

basilisk> Thanks for your super speedy reply!

I've just re-checked the SP it's bailing out on... no usage of any special 'xp_cmd' style procs or functions.

However, I think I've found the issue, I'm guessing it is related to a JOIN to a server that I can't access.

Crisis averted - time to track down the DBA...

Once again, thanks