Friday, December 31, 2010

Cannot find object xxx or you dont have permissions - SP in SQL 2005

One of my colleague wrote a stored procedure in which he was just truncating a table. On calling that SP from a BO developed in .Net 4.0 we got a message as below

"Cannot find object \"xxx\" or you dont have permissions"

The fun part was when we started the SQL Profiler it showed us that it has ran the SP without any errors. There was no error in EVENTVWR too.

On trying multiple options, I ended up giving OWNER rights to NETWORKSERVICE (the login which our service was hosted with) and it worked properly.