While sys.server_principals contains all logins/roles created on the server. sys.database_principals contains all users/roles in the current database. Some of the columns returned by sys.database_principals are as below:

1. Name – name of the principal – user/role name,

2. Principal_id – id of the principal,

3. Type – type of the principal – S = SQL user (mapped to a SQL Login), U = Windows user (mapped to a Windows Login), G = Windows group (mapped to a Windows Group Login), A = Application role, R = Database role, C = Certificate mapped user, K = Asymmetric key mapped user,

4. Type_desc – Textual description of 'Type',

5. Default_schema_name – Default schema name to be used when schema name is not specified,

6. Create/Modify_date – Creation/Modification date,

7. Sid – Security identifier, applicable only to SQL User, Windows User and Windows Group,

8. Is_fixed_role – 1 if it’s a fixed database role.

-- © 2011 – Vishal (http://SqlAndMe.com)
 
SELECT name, principal_id, type, default_schema_name,
       create_date, modify_date, sid, is_fixed_role
FROM   sys.database_principals

Partial Result Set:

Hope This Helps! Cheers!