List of User permissions on all databases for a user

Question

Is there any way can we know the list of user permissions on an sql server instance through script or tool

Answer 1

This can give you a certain insights - however, you cannot really see what a given user has in terms of effective rights (since any rights he might have due to membership in a database role aren't visible per se):

SELECT 
    dp.class,
    dp.class_desc,
    dp.permission_name,
    dp.state_desc, 
    p.name,
    p.principal_id,
    p.type_desc,
    so.name
FROM 
    sys.database_permissions dp
INNER JOIN 
    sys.database_principals p ON dp.grantee_principal_id = p.principal_id
INNER JOIN
    sys.sysobjects so ON dp.major_id = so.id

But basically, the sys.database_permissions (and sys.server_permissions) contain the permission-relevant info which you can join to other system catalog views to get the information you're looking for.

Also, this is on a per-database scope - I don't think there's any way to get the permissions over all databases at once.