Use sp_displaylogin to display information about a specified login account, including any roles granted to that account, where loginame is the user login account about which you want information:
sp_displaylogin [loginame]
If you are not a System Security Officer or System Administrator, you can get information only about your own account. If you are a System Security Officer or System Administrator, you can use the loginame parameter to access information about any account.
sp_displaylogin displays your server user ID, login name, full name, any roles that have been granted to you, date of last password change, default database, default language, and whether your account is locked.
sp_displaylogin displays all roles that have been granted to you, so even if you have made a role inactive with the set command, that role is displayed.
