[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 221
  • Last Modified:

EXECTE AS Permissions problem while trunacating table

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
REVERT

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.
0
Blim2
Asked:
Blim2
1 Solution
 
Blim2Author Commented:
The solution recommended is to create a table truncating procedure with EXECUTE AS SELF. I could then call this from my original procedure and maybe even parameterise it to accept table names. That sounds great but  what happens if I leave the company. Won't EXECUTE AS SELF stamp my credentials on the procedure and won't the procedure therefore stop working if my Windows logon is removed?
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now