none
How can I add a sql user as a sysadmin during SQL Server 2016 Express installation?

    Question

  • I'm trying to find a way to add a sql sysadmin user when I install Microsoft SQL Server 2016 Express.

    Currently I use the following command line syntax to install SQL Server...

    SQLEXPR_x64_ENU.exe /QS /ACTION=Install /FEATURES=SQL 
         /INSTANCENAME=SQL2016EXPRESS /AGTSVCACCOUNT=sqlagent 
         /AGTSVCPASSWORD={PASSWORD} /AGTSVCSTARTUPTYPE=Automatic 
         /SQLSVCACCOUNT=sqldbengine /SQLSVCPASSWORD={PASSWORD} 
         /SECURITYMODE=SQL /SAPWD={PASSWORD} /ADDCURRENTUSERASSQLADMIN 
         /IACCEPTSQLSERVERLICENSETERMS /IACCEPTROPENLICENSETERMS

    I was hoping there was some type of way to do this with a switch (such as /SQLSYSADMINACCOUNTS).  Unfortunately from what I can tell the switch /SQLSYSADMINACCOUNTS only deals with Windows accounts and NOT SQL accounts.

    Is there any way to accomplish my goal while I'm installing SQL Server?

    List of install switches documented on MSDN


    Arvo Bowen III

    Thursday, February 2, 2017 2:47 PM

All replies

  • Hello Arvo,

    During installation the only existing SQL login is the system login "sa", all other SQL Logins must be created after successfull Installation; you can not add one during installation process.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, February 2, 2017 2:58 PM
  • Is it possible to add one after the install completes?  I was hoping via command line.  I'm trying to look into setting a TCP/IP port (1433) on the server via command line so that I can run an sqlcmd.  Any help doing this would be greatly appreciated!


    Arvo Bowen III

    Thursday, February 2, 2017 5:09 PM
  • Hi Arvo Bowen III,

    >> Is it possible to add one after the install completes?

    Yes, and this could be automated via PowerShell:
    $INSTANCENAME = "SQL2016EXPRESS"
    
    Invoke-Expression -command "C:\SQL2016EXPRADV_x64_ENU\Setup.exe /QS /ACTION=Install /FEATURES=SQL /INSTANCENAME=$INSTANCENAME /AGTSVCACCOUNT=""NT AUTHORITY\Network Service"" /AGTSVCSTARTUPTYPE=Automatic /SQLSVCACCOUNT=""NT AUTHORITY\Network Service"" /SECURITYMODE=SQL /SAPWD=""Password01!""/ADDCURRENTUSERASSQLADMIN /IACCEPTSQLSERVERLICENSETERMS /IACCEPTROPENLICENSETERMS"
    
    Sqlcmd -S "(local)\$INSTANCENAME" -E -Q "USE master; CREATE LOGIN [TESTADMIN] WITH PASSWORD=N'Password01!', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
    ALTER SERVER ROLE [sysadmin] ADD MEMBER [TESTADMIN]
    GO" 




    Still, I was wondering how your original code works as the ‘Extract files to’ window pops out every time.

    If you have any other questions, please let me know.

    Regards,
    Lin

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, February 3, 2017 8:01 AM
    Moderator
  • Hi Arvo,

    I’m writing to follow up with you on this post. Was the issue resolved? If the issue has been resolved, please mark the corresponding replies as answer as it would benefit others when they are reading this thread. If not, could you please provide more information so we can have a better understanding about the issue?

    If you have any other questions, please let me know.

    Regards,
    Lin

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, February 20, 2017 8:41 AM
    Moderator