How to Create a SQL Server Login with Username and Password

Creating a SQL Server login requires you to connect to the server first, then use SQL Server Management Studio or a command to add the new user

A SQL Server login is different from a Windows user account — it exists only within SQL Server itself and requires a username and password you create. To add one, you open SQL Server Management Studio (the graphical tool that comes with SQL Server), connect to your server, navigate to the Security folder, right-click Logins, and select New Login. You then type the username you want, choose SQL Server Authentication instead of Windows Authentication, enter and confirm a password, and click OK.

If you do not have SQL Server Management Studio installed, you can create a login using a command-line tool called sqlcmd, but the graphical method is simpler for most people. Either way, the login is created on the specific server instance you connect to — if you have multiple SQL Server installations, you must create the login on each one separately.

Key Takeaways

SQL Server logins are created inside SQL Server itself and require a username and password you define, separate from your Windows user account.
SQL Server Management Studio is the easiest tool to use — you navigate to Security > Logins, right-click, select New Login, and enter your username and password.
You must choose SQL Server Authentication (not Windows Authentication) when creating a password-based login.
The login exists only on the server instance where you create it, so multiple servers require separate logins.
After creating the login, you usually need to grant it permission to use specific databases before it can do anything useful.

Opening SQL Server Management Studio and connecting to your server

Start by opening SQL Server Management Studio on the machine where SQL Server is installed, or on another machine that has the tool installed and network access to the server. When the program opens, you see a Connect to Server dialog. Type the name of your SQL Server instance in the Server name field — this might be just the computer name (like DESKTOP-ABC123) or a named instance (like DESKTOP-ABC123\SQLEXPRESS). Leave Authentication set to Windows Authentication for this connection, because you are connecting as yourself to perform administrative work.

Click Connect. If the connection succeeds, you see the Object Explorer panel on the left side showing your server name at the top. If you get an error, verify that SQL Server is running on that machine and that you have network access to it. Once you are connected, you are ready to create the new login.

Navigating to the Logins folder and creating a new login

In the Object Explorer panel on the left, expand the server name by clicking the arrow next to it. You see a folder called Security — click the arrow next to Security to expand it. Inside, you see a Logins folder. Right-click on Logins and select New Login from the menu that appears.

A New Login dialog opens. This is where you enter the username and password for the new login. The dialog has several tabs, but you only need to work with the General tab for a basic login.

Entering the username and choosing SQL Server Authentication

In the Login name field, type the username you want to create — for example, appuser or dataadmin. This is the name someone will type when connecting to SQL Server with a password.

Below the login name, you see two radio buttons: Windows Authentication and SQL Server Authentication. Make sure SQL Server Authentication is selected. If Windows Authentication is selected, SQL Server will try to use your Windows user account instead of a password, which is not what you want for a new login.

Setting and confirming the password

Once SQL Server Authentication is selected, two password fields appear: Password and Confirm password. Type the password you want in the Password field. SQL Server does not enforce a minimum length or complexity by default, but using a password at least 8 characters long with a mix of letters, numbers, and symbols is a good practice.

Type the same password again in the Confirm password field. If the two do not match, SQL Server will not let you create the login. You can also check the box labeled Enforce password policy if you want SQL Server to require stronger passwords going forward, but this is optional.

Completing the login creation

Once you have entered the username and password, click OK at the bottom of the dialog. SQL Server creates the login and returns you to the main window. The new login now appears in the Logins folder in Object Explorer. You can verify it was created by expanding the Logins folder and looking for the username you entered.

At this point, the login exists but has no permission to do anything. To make it useful, you need to grant it access to one or more databases. Right-click the new login, select Properties, go to the User Mapping tab, and check the boxes next to the databases you want it to access. You can also assign specific roles (like db_datareader or db_datawriter) to control what the login can do within each database.

Creating a login using sqlcmd if you prefer the command line

If you do not have SQL Server Management Studio or prefer to work from the command line, you can create a login using sqlcmd. Open Command Prompt or PowerShell and run a command like this:

sqlcmd -S SERVERNAME -U sa -P password -Q "CREATE LOGIN newusername WITH PASSWORD = 'newpassword'"

Replace SERVERNAME with your server name, sa with a login that has administrative rights (usually the sa account), password with that admin's password, newusername with the login name you want, and newpassword with the password for the new login. The command runs and creates the login without opening any graphical interface. This method is faster if you are creating many logins or automating the process, but it requires you to know the correct syntax and have command-line access to the server.

Testing the new login to make sure it works

After creating the login, test it by opening a new connection in SQL Server Management Studio. In the Connect to Server dialog, type your server name, select SQL Server Authentication, enter the new username and password you just created, and click Connect. If the connection succeeds, the login is working. If you get an error saying the login failed, double-check that you typed the username and password correctly and that the login was actually created.

If the login connects but you cannot see any databases or run queries, the login probably does not have permission to access the databases you need. Go back to the login's properties, add it to the databases you want, and assign the appropriate roles.

Frequently Asked Questions

Can I change the password for a login after I create it?

Yes. Right-click the login in Object Explorer, select Properties, go to the General tab, and enter a new password in the Password field. Click OK to save the change. You can also use the ALTER LOGIN command in sqlcmd if you prefer the command line.

What is the difference between a SQL Server login and a database user?

A login is created at the server level and is what you use to connect to SQL Server. A database user is created inside a specific database and is what controls what that login can do within that database. You create the login first, then create users in each database you want it to access.

What if I forget the password for a login I created?

You cannot recover the password, but you can reset it. Right-click the login, select Properties, go to the General tab, and enter a new password. You need administrative rights to do this. If you are locked out of SQL Server entirely, you may need to restart the server in single-user mode or use the sa account if it is still enabled.

Do I need to restart SQL Server after creating a new login?

No. The login is available when ready after you create it. Anyone with the username and password can connect right away without restarting the server or any services.

Can I create a login that only has permission to read data, not change it?

Yes. After creating the login and adding it to a database, assign it the db_datareader role instead of db_owner or db_datawriter. This role allows it to read data but not insert, update, or delete anything.

This guide is for general information only and is not medical, financial, legal, or other professional advice. For decisions specific to your situation, consult a qualified professional. See our Editorial Policy.