I'm running SQL Server 2012 (I know, ancient…) and trying to work out why users don't have permission to execute some procedures.
I'm logged in as sa
, just in case my own id doesn't have sufficient authority to impersonate.
When I try:
EXECUTE AS LOGIN = 'MBA\SAHFOS_GRP'
I get the message:
Cannot execute as the server principal because the principal "MBA\SAHFOS_GRP" does not exist, this type of principal cannot be impersonated, or you do not have permission.
As far as I can tell, it meets the requirements:
select * from sys.server_principals where name = 'MBA\SAHFOS_GRP'
name principal_id sid type type_desc is_disabled create_date modify_date default_database_name default_language_name credential_id owning_principal_id is_fixed_role
MBA\SAHFOS_GRP 413 0x0105000000000005150000007CEB240DCD7C4166235F636BC8040000 G WINDOWS_GROUP 0 2017-12-01 10:00:45.050 2017-12-01 10:00:45.057 cpr_prod us_english NULL NULL 0
I'm sa
, so surely I have permission. It exists. Which only leaves "cannot be impersonated". Why can't it be impersonated?
Best Answer
This is not supported by design.
Ref: EXECUTE AS Clause (Transact-SQL)
There are workarounds you can use.