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:
- Go to Registry (Start > Run > regedit)
- Go to
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQLServer]
MSSQL10.SQLEXPRESSmay be different, depending on your version of SQL Express
- Change the value of key
- Restart SQLExpress service or restart your computer
Now you can login using with both SQL Server Authentication and Windows Authentication.
- Go to
start > run > cmd, enter
sqlcmdto start sql command
- Create a new user with this command
CREATE LOGIN <login name> WITH PASSWORD = '<password>';
- Edit an existed user with this command
ALTER LOGIN <login name> WITH PASSWORD = '<password>';