Authorizing Additional Connections
Now that you have connected to SQL Server as an administrator, one of your first tasks is to authorize other users to connect. You do this by creating a login and authorizing that login to access a database as a user. Logins can be either Windows Authentication logins, which use your Windows credentials, or SQL Server Authentication logins, which store the authentication information in SQL Server and are independent of your network credentials. Use Windows Authentication whenever possible.
Create a Windows Authentication login
In the previous task, you connected to the Database Engine using Management Studio. In Object Explorer, expand your server instance, expand Security, right-click Logins, and then click New Login.
The Login - New dialog box appears.
On the General page, in the Login name box, type a Windows login in the format <domain>\<login>.
In the Default database box, select AdventureWorks if available. Otherwise select master.
On the Server Roles page, if the new login is to be an administrator, click sysadmin, otherwise leave this blank.
On the User Mapping page, select Map for the AdventureWorks database if it is available. Otherwise select master. Note that the User box is populated with the login. When closed, the dialog box will create this user in the database.
In the Default Schema box, type dbo to map the login to the database administrator schema.
Accept the default settings for the Securables and Status boxes and click OK to create the login.
This is basic information to get you started. SQL Server provides a rich security environment, and security is obviously an important aspect of database operations.