[SQL] Get Specific user PrincipalType & Server Roles & User Mapping & Membership & default schema

[SQL]GetSpecificUserPermissionDetail.sql

/*
 *  Query user name
 */
DECLARE @name SYSNAME = N' input your query account ';

/*
 *  For Server Roles
 */
;with ServerPermsAndRoles as
(
    select
        spr.name as principal_name,
        spr.type_desc as principal_type,
        spm.permission_name collate SQL_Latin1_General_CP1_CI_AS as security_entity,
        'permission' as security_type,
        spm.state_desc
    from sys.server_principals spr
    inner join sys.server_permissions spm
    on spr.principal_id = spm.grantee_principal_id
    where spr.type in ('s', 'u')

    union all

    select
        sp.name as principal_name,
        sp.type_desc as principal_type,
        spr.name as security_entity,
        'role membership' as security_type,
        null as state_desc
    from sys.server_principals sp
    inner join sys.server_role_members srm
    on sp.principal_id = srm.member_principal_id
    inner join sys.server_principals spr
    on srm.role_principal_id = spr.principal_id
    where sp.type in ('s', 'u')
)
select *
from ServerPermsAndRoles
--order by principal_name
where principal_name = @name

/*
 *  For User Mapping 
 */
DECLARE @sql NVARCHAR(MAX) = N''; 
SELECT @sql += N'UNION ALL SELECT N''' + REPLACE(name,'''','''''') + ''',
  CAST (p.name COLLATE SQL_Latin1_General_CP1_CI_AS as varchar(15)) as Name, CAST(p.default_schema_name COLLATE SQL_Latin1_General_CP1_CI_AS as varchar(15)) as default_schema_name, STUFF((SELECT N'','' + r.name 
  FROM ' + QUOTENAME(name) + N'.sys.database_principals AS r
  INNER JOIN ' + QUOTENAME(name) + N'.sys.database_role_members AS rm
   ON r.principal_id = rm.role_principal_id
  WHERE rm.member_principal_id = p.principal_id
  FOR XML PATH, TYPE).value(N''.[1]'',''nvarchar(max)''),1,1,N'''') as Membership
 FROM sys.server_principals AS sp
 LEFT OUTER JOIN ' + QUOTENAME(name) + '.sys.database_principals AS p
 ON sp.sid = p.sid
 WHERE sp.name = @name '
FROM sys.databases WHERE [state] = 0;

SET @sql = STUFF(@sql, 1, 9, N'');

--PRINT @sql;

EXEC master.sys.sp_executesql @sql, N'@name SYSNAME', @name;