Security in SQL Server

A login is a security principal, or an entity that can be authenticated by a secure system. Users need a login to connect to SQL Server

As a security principal, permissions can be granted to logins. The scope of a login is the whole Database Engine. To connect to a specific database on the instance of SQL Server, a login must be mapped to a database user. Permissions inside the database are granted and denied to the database user, not the login. Permissions that have the scope of the whole instance of SQL Server can be granted to a login.

To create a SQL Server login Using SQl Server Management studio:

1. In Object Explorer, expand the folder of the server instance in which you want to create the new login.
   
2. Right-click the Security folder, point to New, and select Login….
   
3. In the Login – New dialog box, on the General page, enter the name of a user in the Login name box. Alternately, click Search… to open the Select User or Group dialog box.
   If you click Search…:
   
   1. Under Select this object type, click Object Types… to open the Object Types dialog box and select any or all of the following: Built-in security principals, Groups, and Users. Built-in security principals and Users are selected by default. When finished, click OK.
      
   2. Under From this location, click Locations… to open the Locations dialog box and select one of the available server locations. When finished, click OK.
      
   3. Under Enter the object name to select (examples), enter the user or group name that you want to find. For more information, see Select Users, Computers, or Groups Dialog Box.
      
   4. Click Advanced… for more advanced search options. For more information, see Select Users, Computers, or Groups Dialog Box - Advanced Page.
      
   5. Click OK.
      
4. To create a login based on a Windows principal, select Windows authentication. This is the default selection.
   
5. To create a login that is saved on a SQL Server database, select SQL Server authentication.
   
   1. In the Password box, enter a password for the new user. Enter that password again into the Confirm Password box.
      
   2. When changing an existing password, select Specify old password, and then type the old password in the Old password box.
      
   3. To enforce password policy options for complexity and enforcement, select Enforce password policy. For more information, see Password Policy. This is a default option when SQL Server authentication is selected.
      
   4. To enforce password policy options for expiration, select Enforce password expiration. Enforce password policy must be selected to enable this checkbox. This is a default option when SQL Server authentication is selected.
      
   5. To force the user to create a new password after the first time the login is used, select User must change password at next login. Enforce password expiration must be selected to enable this checkbox. This is a default option when SQL Server authentication is selected.
      
6. To associate the login with a stand-alone security certificate, select Mapped to certificate and then select the name of an existing certificate from the list.
   
7. To associate the login with a stand-alone asymmetric key, select Mapped to asymmetric key to, and then select the name of an existing key from the list.
   
8. To associate the login with a security credential, select the Mapped to Credential check box, and then either select an existing credential from the list or click Add to create a new credential. To remove a mapping to a security credential from the login, select the credential from Mapped Credentials and click Remove. For more information about credentials in general, see Credentials (Database Engine).
   
9. From the Default database list, select a default database for the login. Master is the default for this option.
   
10. From the Default language list, select a default language for the login.
    
11. Click OK.