SQL Server 2012 provides a wide range of security features designed to help you protect the confidentiality, integrity and availability of data stored in your enterprise databases. One of the most important tasks that database administrators perform is the implementation of 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 so. 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 SQL Server authentication. In Windows authentication mode, you assign all database permissions to Windows accounts. This has the advantage of providing a single sign-on experience for users and simplifying security management. In SQL Server (mixed mode) authentication, you can still assign rights to Windows users, but you may also create accounts that exist only in the context of the database server.
Generally speaking, it's best to use Windows authentication mode as much as possible 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 the organization are fully deprovisioned. However, it's not always possible to meet all of your authentication needs with domain accounts, so you may need to supplement them with local accounts designed to work only with SQL Server databases.
Creating a SQL Server 2012 AccountIf you need to create a SQL Server account when using mixed mode authentication, follow this process for SQL Server 2012:
- Open SQL Server Management Studio.
- Connect to the SQL Server database where you would like to create a login.
- Open the Security folder.
- Right-click on the Logins folder and select New Login.
- If you would like to assign rights to a Windows account, select "Windows authentication". If you would like to create an account that exists only in the database, select "SQL Server authentication".
- Provide the login name in the text box. You may use the Browse button to select an existing account if you chose Windows authentication.
- If you chose SQL Server authentication, you must also provide a password (preferably a strong one!) in both the Password and Confirmation text boxes.
- Customize the default database and language for the account, if desired, using the drop-down boxes at the bottom of the window.
- Click OK to create the account.
Advice for Account CreationHere are some tips you should follow when creating SQL Server 2012 user accounts:
- Be sure to use a strong password if you are creating a SQL Server login.
- If you want to remove an existing account (using either SQL Server authentication or Windows authentication), right-click on the account in the Logins folder and select Delete.
- Creating an account does not create database permissions. The next step of the process is to add permissions to the account.
- SQL Server authentication is only available if you have selected mixed mode authentication for your SQL Server instance.
- Whatever authentication mode you use, be sure to create accounts and assign user privileges with care. Security administration isn't the most glamorous of database administration tasks, but it's one that you want to be sure you get right. You don't want to be the one who has to explain to the boss how the organization's data was compromised or destroyed!