SQL Server Performance

SCHEMA LOCK permission denied on object

Discussion in 'SQL Server 2005 General DBA Questions' started by ramkumar.mu, Sep 28, 2009.

  1. ramkumar.mu New Member

    I got an error while executing a Job that is accessing (SELECTing) a linked server table.
    Error Message
    Msg 229, Sev 14, State 71, Line 1 : SCHEMA LOCK permission denied on object 'SystemIDX', database 'lecus', owner 'dbo'. [SQLSTATE 42000]
    systemIDX is the table in the remote server. and the query that is accessing this table is...
    SELECT TOP (100) PERCENT * FROM dbo.v_lecus_System_Idx
    When running the same job 30 minutes later, it ran fine without errors. And, i did not get that error earlier and after that.
    As per my understanding while creating/modifying execution plan for a query or compiling it will cause a schema stability lock. But what could be the reason to deny permissions on that the first time and allow it next time?
  2. ghemant Moderator

    may be the intermittent issue!!
  3. ramkumar.mu New Member

    This means... this can happen again at anytime?
  4. Adriaan New Member

    Don't know much about schema locks, but I seem to remember they occur when you do a
    SELECT columns
    INTO new_table
    FROM existing_table
    The lock would be on the existing table. This is also the reason why you should avoid the SELECT INTO syntax.
  5. satya Moderator

    Don't you use any LOCKING hints in SELECT queries?
    Check and see if the user has any permissions on the "SYS" schema in the database that he cannot view the table in.

Share This Page