Use sp_securityprofile to display the login profile attributes associated with a login account.
A non-privileged login account can only display the
attributes of a login profile that it is directly associated with,
or the attributes of the default login profile. System security
officer role is required to see attributes and bindings of all login
profiles.
For more syntax information, see sp_securityprofile, in Reference Manual: System Procedures.