How do you add a user to a SQL Server database?

SQL Server 2012 provides a wide range of security features designed to protect the confidentiality, integrity, and availability of data stored in your company’s databases. One of the most important tasks database administrators perform is implementing role-based access control that limits the ability of users to retrieve and modify data in the database unless they have an explicit business need. to do it This requires the identification of individual users through the use of named user accounts.

SQL Server provides two methods for creating database user accounts: Windows Authentication or Mixed Mode, which supports both Windows Authentication and SQL Server Authentication. In Windows Authentication mode, all database permissions are assigned to Windows accounts. This has the benefit of providing a single sign-on experience for users and simplifying security management. In SQL Server authentication (mixed mode), you can still assign rights to Windows users, but you can also create accounts that exist only in the context of the database server.

In general, it is better to use Windows Authentication mode because it reduces the layers of complexity in your environment. By having a single source of user accounts, you can be more confident that users who leave your organization are completely left behind. However, it is not always possible to satisfy all your authentication needs with domain accounts, so you may need to supplement them with local accounts designed to work only with SQL Server databases.

Create a SQL Server 2012 account

If you need to create a SQL Server account when using mixed mode authentication, follow this process for SQL Server 2012:

1. Open SQL Server Management Studio.
2. Connect to the SQL Server database where you want to create a login.
3. Open the Security folder .
4. Right-click the Login folder and select New Login .
5. To assign rights to a Windows account, select Windows Authentication . To create an account that only exists in the database, select SQL Server Authentication .
6. Provide the login name in the text box. You can use the Browse button to select an existing account if you choose Windows Authentication.
7. If you chose SQL Server authentication, you must also provide a strong password in the Password and Confirmation text boxes .
8. Customize the default database and language for the account, if desired, using the dropdown boxes at the bottom of the window.
9. Select OK to create the account.

Tips for creating SQL Server 2012 accounts

Here are some tips to follow when creating SQL Server 2012 user accounts:

• Be sure to use a strong password if you are creating a SQL Server login.
• To delete an existing account (using SQL Server Authentication or Windows Authentication), right-click the account in the Logins folder and select Delete .
• Creating an account does not create database permissions. The next step in the process is to add permissions to the account.
• SQL Server authentication is only available if you have selected mixed mode authentication for your instance of SQL Server.
• Regardless of the authentication mode you use, be sure to create accounts and assign user privileges carefully. Security administration isn’t the most glamorous of database administration tasks, but it’s one you want to make sure you do well.

Note: This applies to SQL Server 2012. If you are using the earlier version of SQL Server 2008, the procedure is the same, but please note that Microsoft stopped supporting SQL Server 2008 in 2014.