You cannot retrieve the password in clear text from SQL Server once it has been set in case that is what you are asking.

To get a 'create login' statement that you can copy and paste to run on another instance for purposes of creating that same exact login somewhere else, what you can do is write a query against the system views and construct the 'create login' statement from the various columns in the view. You'll want to consider if you want to take the SID or not. Doing it this way is not as convenient as using SSMS but it may give you the output you need.

Something like this:

SELECT 'CREATE LOGIN ' + QUOTENAME(name) + ' WITH PASSWORD = ' + sys.fn_varbintohexstr(password_hash) + ' HASHED, ' + 'SID = ' + sys.fn_varbintohexstr(sid)

+ ', DEFAULT_DATABASE = ' + default_database_name + ', DEFAULT_LANGUAGE = ' + default_language_name + ', CHECK_EXPIRATION = '

+ CASE is_expiration_checked

WHEN 0 THEN 'OFF'

ELSE 'ON'

END + ', CHECK_POLICY = ' + CASE is_policy_checked

WHEN 0 THEN 'OFF'

ELSE 'ON'

END + ';' AS create_statement

FROM sys.sql_logins

WHERE name NOT LIKE '##%'

AND name <> 'sa'

UNION ALL

SELECT 'CREATE LOGIN ' + QUOTENAME(name) + ' FROM WINDOWS WITH DEFAULT_DATABASE = ' + default_database_name + ', DEFAULT_LANGUAGE = ' + default_language_name

+ ';'

FROM sys.server_principals

WHERE type IN ( 'U', 'G' )

AND name NOT LIKE 'NT%\%'

ORDER BY create_statement;