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?
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.
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.