2

When using SQL Server Authentication mode, I know that usernames and hashed passwords are saved in the master database. I wonder is there a way to retrieve that information? If yes, which tables stores it? (except contained database users)

Thanks for support.

igelr
  • 2,162
  • 3
  • 26
  • 56

1 Answers1

2

I wonder is there a way to retrieve that information? If yes, which tables stores it? (except contained database users)

For SQL Server authentication you have password stored in hash format you can see it from sys.sql_logins, the column password_hash stores password in hash format. The information about SQl server logins are stored in master database and each login has SID respective to it. Only SA login has same SID no matter what server it is. To get SQL authenticated logins you can use below query

SELECT name
FROM sys.server_principals 
WHERE TYPE = 'S'
and name not like '%##%'

You can use sys.database_principals and sys.server_principal to gain more information about logins and users.

Regarding would you be able to recover the hashed password or decrypt it in some somway, the answer is NO you cannot.

Shanky
  • 19,148
  • 4
  • 37
  • 58