Microsoft SQL Server 7.0 System Administration Training Kit

This lesson looks at creating a plan to allow appropriate user access to resources. It also discusses default logins and roles and their use in this plan.

After this lesson, you will be able to

• Describe the goals of security planning.
• Describe five common considerations when creating a security plan.

Estimated lesson time: 15minutes

Goals in Creating a Security Plan

The goals in creating a security plan are as follows:

• List all of the items and activities in the database that must be controlled through security.
• Identify the individuals and groups in the company.
• Cross-reference the two lists to identify which users can see what data and perform what activities in the database.

This lesson introduces five common considerations that can help you create your security plan.

Determine the Use of Default Logins

In creating a security plan, you need to determine how or whether you will use the sa or BUILTIN\Administrators logins.

The sa Login

Although sa is a built-in administrator login, it should not be used routinely. Instead, system administrators should be members of the sysadmin fixed server role and should log on with their own logins. The sa login cannot be dropped or disabled. Log on as sa if you inadvertently remove all members of sysadmin.

TIP

The BUILTIN\Administrators Login

The local Windows NT group Administrators is automatically mapped to the SQL Server BUILTIN\Administrators login. By default, BUILTIN\Administrators is a member of the sysadmin role.

If you do not want all Windows NT administrators in your organization to have complete access to your SQL Server, you can remove the BUILTIN\Administrators login or remove the login from the sysadmin role. You can replace the login and assign permissions to it if you later decide that you do want to use it.

Another method of limiting the BUILTIN\Administrators login is to remove the Domain Admins global group from the local Administrators group in Windows NT.

Determine public Role Permissions

The public role is a special database role to which every database user belongs. It controls the permissions that all users have by default in each database. You should carefully consider which permissions the public role will have in each database; by default, the public role has no permissions.

Determine the Function of the guest User Account

The guest user account allows a login without a user account to gain access to a database. You should decide whether your databases will have a guest account and, if so, what permissions the guest account should have in your databases. New databases do not have a user called guest. If you wish to enable the guest user in a database, you must add it to the database using SQL Server Enterprise Manager or sp_grantdbaccess. When you add a user called guest to a database with SQL Server Enterprise Manager, you do not have to specify a login name because the guest user is a special user not associated with a login. When you add a user called guest to a database with sp_grantdbaccess, you must specify guest as the login name and as the name in the database.

Map Logins to User Accounts and Roles

Before assigning logins to a database, decide whether you will use user accounts or roles to apply permissions. In general, the following mappings are recommended:

• If members of a Windows NT group are the only ones who perform a series of tasks, create a user account for the group and apply permissions to it.
• If more than one login will perform a group of tasks, create a role and assign the login to the role.
• If a login will perform common administrative tasks, map the login to the appropriate fixed server or database role.

Create Objects with Owner dbo

It is very important to determine which users and roles can create objects in a database. In general, it is recommended that only the sysadmin, db_owner, and db_ddladmin fixed database roles be permitted to create database objects.

It is further recommended that all objects be defined with the dbo user specified as the object owner. Defining objects with dbo as the owner enables any user in the database to refer to the object without including the owner name. Any object created from the sysadmin role has dbo as the owner. From any other role, always specify the dbo user as the owner name when you create the object; otherwise, the object will be created with your user name as the object owner.

Changing Object Owners

If objects were not created with the dbo user as the object owner, you can change the object owner with the sp_changeobjectowner system stored procedure as follows:

Consider the following facts about changing database object owners:

• Only members of the db_owner and db_ddladmin fixed database roles and members of the securityadmin server role can change database object owners.
• Scripts and batch files that included the old owner name in references to the object need to be updated manually. SQL Server cannot perform this update automatically.

Lesson Summary

A security plan looks at the items and activities in a database and the individuals and groups in the company. It cross-references the two, determining who needs access to resources. When creating a security plan, it is important to look at built-in logins, such as sa, and roles, such as db_owner, and decide how they will be used.