Cannot Retrieve User Permissions for All Users

Question

I have the following database and users:

I would like to be able to check what permissions users have to certain tables. I can run the following command and get the permissions for tgrover:

USE ClearToPack
Go
EXECUTE AS USER = N'tgrover';
SELECT DISTINCT permission_name AS Permission
FROM sys.fn_my_permissions(N'ClearToPack.dbo.BoxPart', N'OBJECT')
ORDER BY permission_name;

But if I run the same query using the MyotekUser or ManexUser I receive the following error:

For MyotekUser:

Msg 15517, Level 16, State 1, Line 4
Cannot execute as the database principal because the principal "MyotekUser" does not exist, this type of principal cannot be impersonated, or you do not have permission.

For ManexUser:

Msg 15517, Level 16, State 1, Line 4
Cannot execute as the database principal because the principal "ManexUser" does not exist, this type of principal cannot be impersonated, or you do not have permission.

Why would I not be able to retrieve the permissions for MyotekUser and ManexUser?

I have tried MyotekUser or ManexUser several times.

Answer 1

Not all users can be impersonated - one example is a user created from a certificate.

CREATE DATABASE Impersonate
GO

USE Impersonate

CREATE MASTER KEY  ENCRYPTION BY PASSWORD ='Str0ngPassword!' 

/* Example from MS Docs */
CREATE CERTIFICATE CarnationProduction50  
    WITH SUBJECT = 'Carnation Production Facility Supervisors',  
    EXPIRY_DATE = '11/11/2023';  
GO  
CREATE USER JinghaoLiu FOR CERTIFICATE CarnationProduction50;  
GO

EXECUTE AS user = 'JinghaoLiu'

Msg 15517, Level 16, State 1, Line 12 Cannot execute as the database principal because the principal "JinghaoLiu" does not exist, this type of principal cannot be impersonated, or you do not have permission.

If you can show us the User definition (script it out) it might shed more light on the situation.

But ultimately, this looks like an XY problem. You should get the permissions from correct DMVs instead of impersonating users in a loop.

This can get you started, add or filter the info as you see fit:

SELECT 
    u.name
    , u.type
    , u.type_desc
    , OBJECT_SCHEMA_NAME(t.object_id)
    , t.name
    , dp.permission_name
FROM sys.database_principals AS u
JOIN sys.database_permissions AS dp
    ON dp.grantee_principal_id = u.principal_id
JOIN sys.tables AS t
    ON dp.major_id = t.object_id
    AND dp.class = 1
WHERE u.type IN 
(
      'A' /* = Application role                                                 */
    , 'C' /* = User mapped to a certificate                                     */
    --, 'E' /* = External user from Azure Active Directory                      */
    --, 'G' /* = Windows group                                                  */
    , 'K' /* = User mapped to an asymmetric key                                 */
    --, 'R' /* = Database role                                                  */
    , 'S' /* = SQL user                                                         */
    , 'U' /* = Windows user                                                     */
    , 'X' /* = External group from Azure Active Directory group or applications */
)