Chapter 12 -- Permissions and Security Planning

[Previous] [Next]

Chapter 12

About This Chapter

Logins grant access to the SQL Server. Users and roles grant access to a database. This is analogous to using your cash card and PIN to gain access to an ATM. Before you can withdraw cash from your account, the bank will check to see that you have sufficient funds and that you are not trying to withdraw more than a daily limit. In the same way, every time you attempt to execute statements or use objects in the database, SQL Server will check that you have permission to perform these operations.

Permissions are granted to or revoked from users and roles in the database. It is important to plan the permissions that you grant to each user or role. Remember that a user of a database may be an individual user or a Windows NT group. Each database has its own independent permissions system.

Before You Begin

To complete the lessons in this chapter, you must have

• Experience using SQL Server Enterprise Manager and SQL Server Query Analyzer.
• Knowledge of Windows NT Server user accounts, groups, Windows NT User Manager for Domains, and .CMD (or .BAT) files. This chapter refers to Windows NT User Manager for Domains, although your Windows NT_based computer may display Windows NT User Manager. Both utilities work the same for the purposes of this chapter.

You must also have done the following:

• Configured your Windows NT_based computer to allow the group Everyone to log on locally. This allows you to log on as various users and test different security configurations in the exercises.
• Installed SQL Server version 7 on your computer. Installation procedures are covered in Chapter 2. The exercises assume that you are working on a Windows NT Server configured as a domain controller, although you can complete the exercises using SQL Server installed on a Windows NT Workstation or on a standalone Windows NT Server. You cannot complete all of the exercises for this chapter on SQL Server installed under Windows 95 or Windows 98.
• Created the users and the group listed in the "Before You Begin" section in Chapter 11 in your Windows NT domain. If you did not do this in Chapter 11, you can create these users and groups by using the Windows NT User Manager for Domains, or you can run the batch file makeusrs.cmd located in the C:\Sqladmin\Exercise\Setup folder installed on your hard disk drive from the Supplemental Course Materials CD-ROM.
• Installed the Exercise files from the Supplemental Course Materials CD-ROM to your hard disk drive.
• Installed the StudyNwind database.
• Completed the exercises in Chapter 11 to set up the users and roles needed in the exercises in this chapter. If you have not done this, run the Ch12.cmd batch file in the C:\Sqladmin\Exercise\Setup folder installed on your hard disk drive from the Supplemental Course Materials CD-ROM.