2

My goal is to quietly install SQL Server 2019 Express via command prompt.

Here is the script I have come up with to customize the install (note SETUP.exe is the SQL Server Express installation file):

SETUP.exe /Q /IACCEPTSQLSERVERLICENSETERMS /ACTION=install /FEATURES=SQL 
          /INSTANCENAME=MyInstance /SQLSVCACCOUNT="NT Authority\System" 
          /ADDCURRENTUSERASSQLADMIN /AGTSVCACCOUNT="NT Authority\System" 
          /SECURITYMODE=SQL /SAPWD="Pa$$w0rd" 
          /SQLTEMPDBDIR="C:\MSSQL\TempDB\\" /SQLUSERDBDIR="C:\MSSQL\Data\\" 
          /SQLUSERDBLOGDIR="C:\MSSQL\Log\\"

What I would like to do is create the SQL Server instance with only a new SQL Server authenticated login. The /ADDCURRENTUSERASSQLADMIN parameter tells SQL Server to automatically use the current user as admin. If I set this to false, I have to use /SQLSYSADMINACCOUNTS="Domain\User" which then sets whatever user (if it exists) as a SQL Server Login with Windows authentication.

I want to specify a new SQL Login with password as seen by the /SAPWD="Pa$$w0rd" command. I do get a SQL Server login that is SQL Server authenticated with the login name sa and whatever password I set, however the Windows user still gets full access with Windows authentication. Right now, I can use the sa Login to drop the Windows user login, but this seems inefficient.

Is there a way to accomplish this with my command script?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Smitty-Werben-Jager-Manjenson
  • 480
  • 5
  • 16

1 Answers1

4

You must have some Windows login as a sysadmin, but you can choose one other than the installing user. "NT Authority\System" is a safe choice, as that identity is already capable of taking ownership of the SQL Server instance. And you should use the defaults for service accounts, as it's much more secure to not have such a privileged account as your service account.

So

.\SETUP.exe /Q /IACCEPTSQLSERVERLICENSETERMS /ACTION=install /FEATURES=SQL /INSTANCENAME=MyInstance /SECURITYMODE=SQL /SAPWD="Pa$$w0rd" /SQLSYSADMINACCOUNTS="NT Authority\System" /ADDCURRENTUSERASSQLADMIN=False
David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
  • 1
    Very nice! Thank you. I have my "sa" Login with SQL Authentication and the Windows User Login doesn't seem to have permission to anything. This is what I was looking for. Thanks again! – Smitty-Werben-Jager-Manjenson Jul 15 '20 at 20:17