List users in Azure SQL database

Bart Gawrych - Dataedo Team Bart Gawrych 2019-04-01

Table of Contents:


    Query below returns list of users in current database.

    Users vs logins

    • Login grants access to the server
    • User grants a login access to the database

      One login can be associated with many users but only in different databases

    Query

    select name as username,
           create_date,
           modify_date,
           type_desc as type,
           authentication_type_desc as authentication_type
    from sys.database_principals
    where type not in ('A', 'G', 'R', 'X')
          and sid is not null
    order by username;
    

    Columns

    • username - user name
    • create_date - date the account was added
    • modify_date - date the account was last updated
    • type_desc - principal type:
      • CERTIFICATE_MAPPED_USER - User mapped to a certificate
      • EXTERNAL_USER - External user from Azure Active Directory
      • ASYMMETRIC_KEY_MAPPED_USER - User mapped to an asymmetric key
      • SQL_USER - SQL user
      • WINDOWS_USER - Windows user
    • authentication_type - type of user authentication
      • NONE : No authentication
      • INSTANCE : Instance authentication
      • DATABASE : Database authentication
      • WINDOWS : Windows Authentication

    Rows

    • One row represents one user in the database
    • Scope of rows: all users in the database
    • Ordered by user name

    Sample results

    Those results show that there are 5 users in current database.

    sample results

    SSMS

    You can vew database users using SQL Server Management studio. Expand Database -> Security -> Users branch in Object Explorer. This option shows also roles and Windows groups.

    ssms

    0
    There are no comments. Click here to write the first comment.

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy.
    Accept