The postings on this site are my own and do not represent my Employer's positions, advice or strategies.

LifeAsBob - Blog

 

Home

No Ads ever, except search!
Wednesday, September 28, 2022 Login
Public

Blog posts for the month of November,2019.
Only active directory users can impersonate other active directory users.11/15/2019 3:04:49 PM

Msg 33171, Level 16, State 1, Procedure TRUNCATE_TBL, Line 0 [Batch Start Line 11] Only active directory users can impersonate other active directory users.

refer to
https://docs.microsoft.com/en-us/sql/t-sql/statements/execute-as-clause-transact-sql?view=sql-server-ver15

also check the following for troubleshooting this, specifically the database owner on azure and the execution context.

truncate table,

review script make sure proc is dba.


1.  is it in the list of authorized tables dba.truncate_list

2.  is the dbowner valid (not mapped to a guid, should be sql of azure (sqladmin01 or dadmin01).

3.  make sure the procedure itself is correct (execute as self)

4.  the execution context is mapped to dbo, and not some other account 

- run script from sqlmonitor showing execution context.


select object_schema_name(sm.object_id) as [schema],object_name(sm.object_id),sm.execute_as_principal_id,sp.[name]

from sys.sql_modules sm

left join sys.database_principals sp on sm.execute_as_principal_id = sp.principal_id

where execute_as_principal_id is not null


Blog Home