Cannot execute as the server principal… with Windows (Active Directory) Group login

All we need is an easy explanation of the problem, so here it is.

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

This is not supported by design.

Ref: EXECUTE AS Clause (Transact-SQL)

Specifies the statements inside the module execute in the context of
the user specified in user_name. Permissions for any objects within
the module are verified against user_name. user_name cannot be
specified for DDL triggers with server scope or logon triggers. Use
login_name instead.

user_name must exist in the current database and must be a singleton
account. user_name cannot be a group, role, certificate, key, or
built-in account, such as NT AUTHORITY\LocalService, NT
AUTHORITY\NetworkService, or NT AUTHORITY\LocalSystem.

There are workarounds you can use.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply