Skip to main content
Database Administrators

Return to Answer

Add permission script
Source Link
Zikato
Zikato
  • 5.5k
  • 1
  • 16
  • 34

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 */
)

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 */
)
Source Link
Zikato
Zikato
  • 5.5k
  • 1
  • 16
  • 34

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.