Log Shipping – NORECOVERY vs. STANDBY mode

 

Here is the query that lists all the user permissions, you need to run this on proper database to list the permissions, You can also put a filter on user

 

WITH cteUserPermissions
AS
(
-- Permissions provisioned to a sql user 
-- or windows user/group directly 
SELECT  
         [UserName] = CASE DP.[type] 
                                         WHEN 'S' THEN DP.[name]
                                         WHEN 'U' THEN L.[name]
                                  END,
         [UserType] = CASE DP.[type]
                                         WHEN 'S' THEN 'SQL User'
                                         WHEN 'U' THEN 'Windows User'
                                  END,  
         [DatabaseUserName] = DP.[name],       
         [Role] = null,      
         [PermissionState] = P.[state_desc],
         [PermissionName] = P.permission_name,
         [ObjectName] = OBJECT_NAME(P.major_id),
         [ObjectType] = O.type_desc    
FROM        
         sys.database_principals DP
         LEFT JOIN sys.login_token L ON DP.[sid] = L.[sid]
         LEFT JOIN sys.database_permissions P ON P.[grantee_principal_id] = DP.[principal_id]
         LEFT JOIN sys.objects O ON P.[major_id] = O.[object_id]
WHERE DP.[type] in ('S','U')   

UNION

-- Permissions provisioned to a sql user or windows user/group 
-- through a database or application role
SELECT  
         [UserName] = CASE M.[type] 
                                         WHEN 'S' THEN M.[name]
                                         WHEN 'U' THEN L.[name]
                                  END,
         [UserType] = CASE M.[type]
                                         WHEN 'S' THEN 'SQL User'
                                         WHEN 'U' THEN 'Windows User'
                                  END, 
         [DatabaseUserName] = M.[name],   
         [Role] = R.[name],      
         [PermissionState] = P.[state_desc],
         [PermissionName] = P.permission_name,
         [ObjectName] = OBJECT_NAME(P.major_id),
         [ObjectType] = O.type_desc
     
FROM        
         sys.database_role_members DRM
         JOIN sys.database_principals R ON R.[principal_id] = DRM.[role_principal_id]
         JOIN sys.database_principals M ON M.[principal_id] = DRM.[member_principal_id]
         LEFT JOIN sys.login_token L ON M.[sid] = L.[sid]
         LEFT JOIN sys.database_permissions P ON P.[grantee_principal_id] = R.[principal_id]
         LEFT JOIN sys.objects O ON P.[major_id] = O.[object_id]

UNION

-- Permissions provisioned to the public role, 
-- which everyone gets by default
SELECT  
         [UserName] = '{All Users}',
         [UserType] = '{All Users}', 
         [DatabaseUserName] = '{All Users}',       
         [Role] = DP.[name],      
         [PermissionState] = P.[state_desc],
         [PermissionName] = P.permission_name,
         [ObjectName] = OBJECT_NAME(P.major_id),
         [ObjectType] = O.type_desc
FROM
         sys.database_principals DP
         LEFT JOIN sys.database_permissions P ON P.[grantee_principal_id] = DP.[principal_id]
         JOIN sys.objects O ON O.[object_id] = P.[major_id]
WHERE
         DP.[type] = 'R'                         -- only roles
         AND DP.[name] = 'public'        -- only public role
         AND O.is_ms_shipped = 0 
)
SELECT *
FROM cteUserPermissions
--WHERE UserName in ('{All Users}', 'myusername')
ORDER BY UserName, ObjectName, PermissionState, PermissionName