Find invalid objects - insufficient permissions?

basiliskbasilisk Posts: 4
edited June 26, 2011 11:35PM in SQL Prompt Previous Versions
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
· Share on Twitter

Comments

  • Paul StephensonPaul Stephenson Posts: 125
    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
    Paul Stephenson
    Project Manager, Red Gate
    · Share on Twitter
  • basiliskbasilisk Posts: 4
    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
    · Share on Twitter
  • mikefmikef Posts: 5 Bronze 2
    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!
    · Share on Twitter
  • basiliskbasilisk Posts: 4
    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.
    · Share on Twitter
  • mikefmikef Posts: 5 Bronze 2
    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 :)
    · Share on Twitter
Sign In or Register to comment.