Part -1:
Configuring the Authentication Mode in SSMS
In this exercise, you will configure the authentication mode for the SQL Server instance. You will ensure that the authentication mode is set to mixed mode so that the remaining exercises in this chapter will function properly. To do this:
1. Launch SSMS.
2. Right-click on the SQL Server instance you want to configure and select Properties.
3. Select the Security page to view the authentication mode settings.
4. Ensure that the authentication mode is set to SQL Server and Windows authentication mode, which is mixed mode.
5. Click OK to save the changes.
6. If you see a message indicating that the service must be restarted, right-click on the instance and select Restart.
Part -2:
Creating a SQL Login
In this exercise, you will create a SQL login for a user named Monty with a password of 7Pass8now.
1. Launch SSMS.
2. Expand the Security section in Object Explorer.
3. Right-click the Logins container and select New Login.
4. Enter the word Monty in the Login name field.
5. Check the SQL Server authentication radio button.
6. Enter 7Pass8now in both the Password and Confirm Password fields.
7. Accept all other defaults, click the selector arrow next to the Script button, and choose Script Action to New Query Window.
8. Click the OK button. (If you are running SQL Server on Windows XP, you may have to clear the User Must Change Password at Next Login check box.)
9. Notice the code in the new query window is similar to the following:
CREATE LOGIN [monty] WITH PASSWORD=N'7Pass8now ‘ MUST_CHANGE,
DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
Part- 3:
Viewing Local password policies
In this exercise, you will view the password policies on a non-domain member server that is running SQL Server 2008. Domain member SQL Servers receive their password policies from the domain. Only the Domain Admins and the Enterprise Admins group members can manage the domain password policies. To view the local password policies, follow these steps:
1. Click Start and select Run.
2. Type gpedit.msc into the Open field and click OK.
3. In the Local Computer Policy\Computer Configuration section, expand the Windows Settings container.
4. Expand the Security Settings container.
5. Expand the Account Policies container.
6. Click on the Password Policy container. From here, you can view and manage the local password policies.
Part -4:
Creating Windows Logins
In this exercise, you will create a Windows login by first creating a Windows user account and then a Windows group account. Next, you will map these accounts to a Windows login in SQL Server 2008. To do this, follow these steps. Please note that this exercise assumes the use of local users and groups on Windows Server 2008.
1. Right-click My Computer on your SQL Server machine's desktop and select Manage.
2. Expand the Configuration container and then the Local Users and Groups container.
3. Right-click the Users container and select New User.
4. Enter Jeremy in the User Name field.
5. Enter 7pass8now in both the Password and Confirm Password fields.
6. Deselect the User Must Change Password at Next Logon check box.
7. Click the Create button to create the user account. Click the Close button to close the dialog.
8. Right-click the Groups container and select New Group.
9. For the Group name field, enter sQLusers.
10. In the Description field, enter users with access to sQL server.
11. Click the Create button to create the group.
12. Click the Close button to close the New Group dialog.
13. Close the Server Manager application.
14. Launch SQL Server Management Studio.
15. Expand the Security container in Object Explorer.
16. Right-click the Logins container and select New Login.
17. Click the Search button to find the account you created for Jeremy.
18. Type Jeremy in the Enter the Object Name to Select field and click the Check Names button. The dialog will automatically enter the full name for the user, which includes the server name and the username. A domain user would have a format like this: domainName\userName.
19. Click OK.
20. In the New Login dialog, click OK again to create the mapping to the Windows user account.
21. To create the mapping for the SQLUsers group, right-click the Logins container and select New Login.
22. Click the Search button to search for the SQLUsers group.
23. Click the Object Types button to bring up the Object Types dialog box and then check the Groups check box (the other check boxes will already be checked) and then click OK.
24. Type sQLusers in the Enter the Object Name to Select field and click the Check Names button.
25. Click the OK button.
26. Click the OK button in the New Login dialog to create the group-based login.
Part -5:
Creating a database role with SSMS
In this exercise, you will perform the simple steps required to create a database role using SSMS. To do this:
1. Expand the Databases container.
2. Expand the container for the database in which you want to create a custom or application role.
3. Expand the Security container and right-click on the Roles container to select New -> Database Role or New -> Application Role.
The following graphic shows the results of creating a new database role in SSMS.
Part -6:
Creating a database user
In this exercise, you will create a database user. You will create the user with the CREATE USER T-SQL command. You will also create an account for a user named Fred in the database named Books. To do this, follow these steps:
1. Launch SQL Server Management Studio.
2. Connect to the SQL Server instance containing the database in which you want to create a user. Log in as an administrator.
3. Click the New Query button.
4. In the new query window, enter the following code.
USE Books;
GO
CREATE USER fred;
GO
5. Click the Execute button to run the code.