2

Where is the password saved when creating a contained user for the Azure SQL database (PaaS)?

For example:

CREATE USER taiobtest WITH password='PVHz3U4A$LNytQF^';
GO

For server logins I can get it from sys.sql_logins:password_hash column.

My use case is refreshing non-production from production backup. I need to script out the database contained users and all relevant permission before the restoration. Once restored clean up production database users/permission and reinstate non-production users and permissions.

3
  • I don't think it is available through any catalog view (but I don't have an instance to play with here). Possibly if you connect using DAC and query system tables directly. The question is what you want to do with the hash? It comes with the database, so you don't have the same usage as you do for a login. May 9 at 17:40
  • My use case is refreshing non-production from production backup. I need to script out the database containing users and all relevant permission before the restoration. Once restored clean up production database user/permission and reinstate non-production user and permission. May 10 at 12:26
  • 1
    Understood. Thing is that CREATE LOGIN has a HASHED option for when you specifies the password. CREATE USER doesn't have that option. So even if you managed to get the hash for the user's password you won't be able to use it. This is something you could ask for at the feedback forum (feedback.azure.com/d365community/forum/…), but that won't help you at the moment... May 11 at 6:53

1 Answer 1

3

As Tibor points out, the practical answer here is "in the database", so it follows the database through backup/restore or an AG failover. That is the reason database users with passwords were added to partially contained databases and Azure SQL Database.

If you examine the object_definition of sys.database_principals you can discover that it's actually in a system table called sys.sysowners, stored as a hash, which can only be queried from the Dedicated Administrator Connection.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.