Cannot Retrieve User Permissions for All Users



  • I have the following database and users:

    enter image description here

    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;
    

    enter image description here

    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.



  • Not all users can be impersonated - one example is a https://docs.microsoft.com/en-us/sql/t-sql/statements/create-user-transact-sql?view=sql-server-ver15#c-creating-a-database-user-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 https://xyproblem.info/ . 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 */
    )
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2