none
Where is the SQL logon acct created during deployment of the azure sql database? RRS feed

  • Question


  • After creating the azure sql database I noted that zzadmin (SQL account) is added to the Master database below.  However under the master database there is no zzadmin.   zzadmin appears in ARM under both properties of the sql server and the database.  This is the sql account that is not associated w AD.  I am able to logon SSMC.

    Why does the sql logon account (zzadmin) appear under the Master database but does not appear under the database (sqldb001)?


    dsk


    • Edited by kimdav111 Thursday, August 22, 2019 11:55 PM
    Thursday, August 22, 2019 11:45 PM

All replies


  • After creating the azure sql database I noted that zzadmin (SQL account) is added to the Master database below.  However under the master database there is no zzadmin.   zzadmin appears in ARM under both properties of the sql server and the database.  This is the sql account that is not associated w AD.  I am able to logon SSMC.

    Why does the sql logon account (zzadmin) appear under the Master database but does not appear under the database (sqldb001)?


    dsk

    Good day kimdav,

    >> I noted that zzadmin (SQL account) is added to the Master database below.  However under the master database there is no zzadmin.

    Not clear to me.
    "under the master database there is no zzadmin", so how do know it was "added to the Master database"?
    1. Please clarify how you checked this.
    2. Can you show screenshot of what you see and where you do not see what you expect to see?

    In the meantime... regarding the final question this is clear :-)

    >> Why does the sql logon account (zzadmin) appear under the Master database but does not appear under the database (sqldb001)?

    It seems like you confuse LOGIN and USER

    The LOGIN is a server level entity.
    When you connect to the server using SSMS, do you see it under the "server name" -> "Security" ?

    The LOGIN does not appears in the database level.
    In the database level we have USER entities.

    By default in the Azure Database, a new USER is created in the master database which has the same name as the LOGIN. This is not the LOGIN but a different entity - a USER. This is done under the scenes for you by executing the command

    CREATE USER [XXX] FOR LOGIN [XXX] WITH DEFAULT_SCHEMA=[dbo]
    GO

    Where XXX is the LOGIN you choose when you created the Azure logical server.

    You can create USER using the same command yourself if you want in any database.

    Do you see the LOGIN as in the image above by missing the USER in the master?

    Please execute the following commands:

    -- Get the LOGIN
    SELECT * FROM sys.sql_logins;
    GO
    -- GET THE USER:
    -- Note! for this you need to connact to the specific database
    -- Note! This return users and Roles
    SELECT * FROM sys.sysusers;
    GO

    Check these tutorials on USER and LOGIN for more information:

    https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/create-a-database-user?view=sql-server-2017

    https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/create-a-login?view=sql-server-2017


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]
    Friday, August 23, 2019 8:16 AM
    Moderator
  • From your description I am guessing that zzadmin is your Server Admin, as set in the Azure Portal.

    This account will show up inside the master database, but not in user databases. Consider this similar to the behaviour of sysadmin on-prem if you are familiar with that. 


    Andreas Wolter (Blog | Twitter)
    Senior Program Manager SQL Server & Azure Security

    MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012

    Friday, August 23, 2019 12:25 PM
  • Hi Andreas,

    I sent you private message on Facebook
    please check😃


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Sunday, August 25, 2019 4:56 PM
    Moderator
  • ...

    This account will show up inside the master database, but not in user databases. Consider this similar to the behaviour of sysadmin on-prem if you are familiar with that. 


    ...

    allow me to clarify my own remark:
    The similarity is that as a sysadmin in SQL Server you do not need to have a User inside any user database. You are matched to the dbo in every database by default.

    The master-database in Azure SQL Database however is a little different. This is where a Server Admin and AAD Admin will appear as a User. And the Server Admin will also be manifested as a Login. - There is no sysadmin server role though.


    Andreas Wolter (Blog | Twitter)
    Senior Program Manager SQL Server & Azure Security

    MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012

    Monday, August 26, 2019 11:04 PM
  • Hi kimdav11

    Please let us know if you want any further information. 

    Thanks
    Navtej S

    • Marked as answer by kimdav111 Tuesday, August 27, 2019 8:41 PM
    • Unmarked as answer by kimdav111 Tuesday, August 27, 2019 8:45 PM
    Tuesday, August 27, 2019 2:37 PM
    Moderator