EXECTE AS Permissions problem while trunacating table
Posted on 2009-05-19
I have a stored procedure, called from Access as a pass through query, that works for me but not for the users. The error message is:
'Cannot find the object "AgrRelations_Materials" because it does not exist or you do not have permissions.'
This procedure truncates then recreates some lookup tables. After a bit of Googling I have come to the conclusion that the problem is due to the TRUNCATE statements which, apparently, do not follow the normal pattern of ownership chaining (which I had never heard of until today). I have kind of confirmed this by replacing the TRUNCATE statements with delete statements, and the users can now use the procedure.
However, I wanted to use TRUNCATE as it is faster, so I tried wrapping an EXECUTE AS then REVERT around the TRUNCATE statements:
EXECUTE AS USER = 'dbo'
TRUNCATE TABLE Relations.AgrRelations_Materials
Although this still works for me the users get the following:
Cannot execute as the database principal because principal dbo does not exist, this type of principal cannot be impersonated, or you do not have the permission.
dbo is a user in this database and owns most of the schemas, including the Relations schema that these tables are in.
More Googling and I am stuck. The database has not been restored and does not meet the criteria in kb/913423.
Any help gratefully received: Im a relative newbie and I expect this will come up a lot.