Create a login user for SQL Express Server

This article is about Microsoft SQL Server Express Edition, not to be confused with its mother version SQL Server, nor MySQL.

SQL Express is lightweight, swift and powerful, but the default version that ships with Visual Studio does not let you login using the “sysadmin” account — instead, it uses your Windows user as an authentication. In fact, Windows Authentication is the default authentication mode and it’s not easy to change it into SQL Server Authentication (username and password pair).

In case you want to use the SQL Server Authentication, here’s how:

  1. Go to Registry (Start > Run > regedit)
  2. Go to

    [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQLServer]

    Note: MSSQL10.SQLEXPRESS may be different, depending on your version of SQL Express

  3. Change the value of key LoginMode into 2 (hexadecimal)
  4. Restart SQLExpress service or restart your computer

Now you can login using with both SQL Server Authentication and Windows Authentication.

In case you don’t have any SQL account, follow these instruction to create (or edit) one. In short:

  1. Go to start > run > cmd, enter sqlcmd to start sql command
  2. Create a new user with this command

    CREATE LOGIN <login name> WITH PASSWORD = '<password>';
    GO

    More reference.

  3. Edit an existed user with this command

    ALTER LOGIN <login name> WITH PASSWORD = '<password>';
    GO

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s