Table of Contents

Chapter 9

Managing Security

Certification Objectives *

Windows NT Authentication Mode *

SQL Server Authentication *

From the Classroom *

Physical Security *

SQL Server Login Accounts *

From the Classroom *

Auditing SQL Server Security *

Built-In Administrator Account *

Windows NT Authentication versus SQL Server Authentication *

Fixed Server *

Fixed Database *

Public *

User-Defined Database *

Application *

Object Permissions *

Statement Permissions *

Implied Permissions *

Enabling Access to Remote Data *

Linked Servers *

Setting Up Security for Linked Databases *

Certification Objectives

Imagine that you work for a Fortune 100 company that depends on databases to keep track of all of the important business transactions that occur. What could happen to the company if a competitor, or a hacker hired by your competitor, was able to easily access your databases? As you can imagine, devastation could occur very quickly. The competitor or hacker could alter or delete transactions, or even delete the entire database. At the very least they could gather valuable intelligence from the data contained within the databases. This is the reason that SQL Server 7.0 has built-in security to help protect your databases.

In the first section of this chapter we will discuss the methods available to implement login authentication for SQL Server 7.0. After login authentication, we move on to a discussion on how to create and assign various roles that are supported by SQL Server. In the third and fourth sections of the chapter, we discuss how to implement permissions and obtain information on those permissions. We wrap up the chapter by taking a look at how to resolve broken ownership chains.

Implementing Login Authentication

SQL Server 7.0 has the capability to operate in one of two different login authentication modes. It can use either the Windows NT authentication mode, or the Mixed mode. Mixed mode allows users to connect using Windows NT Authentication or SQL Server Authentication. No matter which authentication mode is used to connect to SQL Server, the security mechanism for both modes is the same.

Windows NT Authentication Mode

The Windows NT Authentication mode of SQL Server 7.0 integrates login security with Windows NT. It integrates this security by utilizing the security credentials of a network user to control login access. The network security credentials for a user is established when they first login to the network. SQL Server uses Windows NT to determine the validated network username when a network user first tries to connect. Whether login access is permitted or denied depends on how Windows NT answers SQL Server in regards to the network username. This is accomplished without requiring a separate login name and password from the network user for SQL Server. This type of connection is called a trusted connection. Figure 9-1 shows a SQL Server that is configured for Windows NT Authentication mode only.

Figure 1: SQL Server configured for only Windows NT Authentication

Certain situations occur when SQL Server is configured to use the Windows NT Authentication mode only. If a user attempts to login from an application using a specific login, then SQL Server ignores it and sticks with using only Windows NT Authentication. This prevents a user from trying to impersonate someone with higher privileges.

Exam Watch: Windows NT Authentication Mode is not available when SQL Server is running on Windows 95 or Windows 98. It is available when Windows 95 and Windows 98 clients access SQL Server on Windows NT.

SQL Server Authentication

SQL Server Authentication depends on authentication it performs itself. It performs this by verifying the SQL Server login account and password when a user attempts to login. If no login account exists, or the password is incorrect, then authentication fails and SQL Server sends an error message to the user (as shown in Figure 9-2). This type of connection is called a non-trusted connection.

fig 9-2.gif (3660 bytes)

Figure 2: Failed Login for a User Account that Input an Incorrect Password

There are three reasons that SQL Server Authentication is provided in SQL Server 7.0. The first reason is to maintain backward compatibility with earlier versions of SQL Server. Some applications may require the use of SQL Server login accounts and passwords. The second reason it is provided is as follows; if you run SQL Server 7.0 on either Windows 95 or Windows 98, then you cannot use the Windows NT Authentication mode. This situation exists because it is not supported on those platforms. Even though SQL Server 7.0 uses Mixed mode when running on the Windows 95/98 platforms, it only supports SQL Server Authentication. The third reason it is provided is because authentication is required for certain connection types, such as over the Internet.

SQL Server will automatically switch from SQL Server Authentication to Windows NT Authentication if someone attempts to login to SQL Server 7.0 using a blank login name.

From the Classroom

Physical Security

The best Windows NT and SQL security can easily be bypassed by obtaining physical access to either the server or the backup media. With access to your backup tapes, a copy of your database can be loaded onto another SQL server where the data can be examined without fear of being caught. Access to the NT server can give access to the NT Security Account Manager (SAM). Then the account name and password of the local NT administrator account can generally be easily obtained using tools available on the Internet. It is vitally important that adequate security measures be taken to ensure your data and servers are physically secure.

—David Smith, MCSE + Internet

Exam Watch: Login accounts and passwords are case-sensitive if you selected a case-sensitive sort order when SQL Server was installed.

SQL Server Login Accounts

If you decide to use the SQL Server Authentication portion of Mixed mode, then you need to create user accounts. These accounts need to be created so people can log in to the server. There are three methods of creating a new user account. The first method is to use the Create Login Wizard, as shown in Figure 9-3. System administrators that are new to SQL Server probably use this method most often.

Figure 3: The First Screen of the Create Login Wizard Used to Create SQL Server Login Accounts

From the Classroom

Auditing SQL Server Security

Sooner or later your internal or external auditors will want to examine the security layout of your SQL Server. It is best to involve them early in the process to ensure you are complying with what the auditors will want to see in place. It is very difficult to secure a server after it is in production. Therefore, the effort up front will be well worth the work.

—David Smith, MCSE + Internet

The second method to create new login accounts is from within SQL Server Enterprise Manager. Choose New Login from the menu shown when you right-click on Logins from SQL Server. This action presents you with the General tab of the SQL Server Login Properties window, as shown in Figure 9-4. On the General tab you give a name to the login, as well as specify the default database and language that is used.

Figure 4: The General Tab of the SQL Server Login Properties Shown While Creating a New Login Account

Two other tabs are available when you create a new login account: Server Roles and Database Access. We discuss Server Roles in the next section of this chapter. The Database Access tab (as shown in Figure 9-5) allows you to specify two things: the database and the role within the database that the login will be able to access.

Figure 5: The Database Access Tab of the SQL Server Login Properties Shown While Creating a New Login Account

The third method of creating a new login account consists of using the system stored procedure sp_addlogin in a Transact-SQL statement. To be able to use the sp_addlogin system stored procedure, you must be a member of the sysadmin or securityadmin fixed server roles.

There are several arguments available from sp_addlogin that enable the inclusion of the login name, password, default database, and default language. The password is encrypted unless told otherwise, and the default database is master unless specified otherwise. SQL Server logins and passwords can contain from 1 to 128 characters, including letters, symbols, and numbers. There are three items, however, that logins cannot contain:

Let’s take a look at a few examples of the sp_addlogin system stored procedure. The first example creates a new login for Raymond with no password and a default database of master.

EXEC sp_addlogin 'Raymond'

The next example creates a new login for Janie, with a password of gramita and a default database of crafts.

EXEC sp_addlogin 'Janie', 'gramita', 'crafts'

The last example creates a new login for Franklin, with a password of trucker, a default database of packages, and a default language of Spanish.

EXEC sp_addlogin 'Franklin', 'trucker', 'packages', 'spanish'

Built-In Administrator Account

SQL Server comes with a built-in administrator account named sa (system administrator). If you are using Mixed mode, then you must use the sa account to administer your SQL Server the first time you login.

The sa login is a special login that belongs to the sysadmin fixed server role. As mentioned earlier, we discuss server roles later in the chapter. The sa account cannot be changed to another server role. If you attempt to change the sa account, you will see the dialog box shown in Figure 9-6. This appeared when I tried to remove sa from the System Administrator server role. The sa account is provided in SQL Server 7.0 to maintain backward compatibility with earlier versions of SQL Server. Normally, you should not use the sa account. All system administrators should have their own logins created, and use those to log in to SQL Server. The sa account should only be used when there is no other way for you to log in to SQL Server.

Figure 6: Error Message Box Displayed when Trying to Modify the sa Login Account

On the Job: The sa login by default does not have a password when SQL Server is initially installed. You need to immediately assign a password to the sa account after installation. This will prevent unauthorized access to SQL Server by someone using that account.

Windows NT Authentication versus SQL Server Authentication

Should you use Mixed mode or only Windows NT Authentication? Now that you are familiar with the login authentication methods available in SQL Server 7.0, let’s compare them to see which may be right for your circumstances.

A SQL Server that uses only Windows NT Authentication has benefits over one that also uses SQL Server Authentication. First, you do not have to create a separate SQL Server account for each individual that needs to access your databases. Second, Windows NT security provides more features than SQL Server Authentication. These features include password expiration, minimum password length, and capability to lockout accounts after a certain number of wrong password attempts. Third, SQL Server reads information about users and groups when the user connects. Windows NT , however, maintains the user and group information. Any changes made to a connected user are effective the next time the user connects to SQL Server.

A SQL Server that uses Mixed mode may be easier to manage than Windows NT Authentication only systems. This is especially true for databases that are not mission-critical, or that contain confidential data. This is because you can create a single SQL Server login and password for all users of a database instead of creating several Windows NT user and group accounts. The downfall to using this method is that it removes the capability to monitor the activities of individual users.

Exercise 9-1 gives you the opportunity to practice implementing login authentication for SQL Server 7.0. In this exercise, you will configure SQL Server to use only Windows NT Authentication.

Exercise 9-1: Implementing Login Authentication

  1. Click the Start button and select Programs | Microsoft SQL Server 7.0 | Enterprise Manager.
  2. Select a SQL Server Group and click the + to expand it.
  3. Right click on a server and choose Properties from the menu.
  4. Select the Security tab.
  5. Click the radio button to the left of Windows NT only.
  6. Click the OK button. The authentication mode for the SQL Server you selected has been changed.

After a user has been authenticated by SQL Server 7.0 using their login, they have permission to access any database in which they have a user account. The reason that user accounts are required in the databases is as follows; to prevent users from being able to connect to SQL Server and access all the databases on the server.

Creating and Assigning SQL Server Roles

Login accounts can be assigned different roles in different databases depending on the needs that are required. Roles are similar to Windows NT groups. They are similar in that they allow you to collect users into a single component against which you can apply permissions. Permissions granted to, denied to, or revoked from a role apply to any members of the role. The permissions are automatically applied when users become members of the role.

Users are not limited to being only a part of a single role. It is possible for them to belong to multiple roles in SQL Server 7.0. In this section we discuss the different roles available in SQL Server 7.0. We also discuss how to create additional roles if necessary.

Fixed Server

The first type of role I want to discuss is the fixed server role. Fixed server roles exist at the server level. Therefore, they are external to individual databases. To add a user to a fixed server role, the person must have a SQL Server or Windows NT login account. An important point to remember is that any member of a fixed server role can add other logins to that role. Table 9-1 shows the seven fixed server roles that are available in SQL Server 7.0. It is not possible to add any new fixed server roles to the seven that already exist.

Fixed Server Role Description
dbcreator Can create and alter databases.
diskadmin Can manage the disk files.
processadmin Can manage the processes running in SQL Server.
securityadmin Can manage the logins for the server.
serveradmin Can configure the server-wide settings.
setupadmin Can install replication and manage extended procedures.
sysadmin Can perform any activity in the SQL Server installation.

Table 1: Descriptions for the Seven Fixed Server Roles in SQL Server 7.0

There are two methods normally used to add users to a fixed server role. The first method is to use SQL Server Enterprise Manager. Where you add a user to a fixed server role depends on whether you are creating the login account or it already exists. If you are creating the login, then you use the Server Roles tab as shown in Figure 9-7.

Figure 7: The Server Roles Tab of the SQL Server Login Properties shown while creating a New Login Account

You can also use SQL Server Enterprise Manager if the login account already exists. In this case you highlight Server Roles from SQL Server as shown in Figure 9-8. In the right pane right-click on the role you want to add a user or users to. Next click Properties, and then click the Add button.

fig 9-8.gif (17238 bytes)

Figure 8: The Fixed Server Roles Available for the P233 SQL Server

The second method is to the sp_addsrvrolemember system stored procedure in a Transact-SQL statement. Only members of the fixed server roles can execute sp_addsrvrolemember. The sp_addsrvrolemember system stored procedure accepts two arguments, login name and role name. The following example adds the Windows NT user sdc\martha to the securityadmin fixed server role.

EXEC sp_addsrvrolemember 'sdc\martha', 'securityadmin'

Exam Watch: Any Windows NT user that belongs to the BUILTIN\Administrators group is automatically added as a member of the sysadmin fixed server role.

Fixed Database

Another type of role available in SQL Server 7.0 is the fixed database role. Fixed database roles are defined at the database level and exist within each database. To add a user to a fixed database role, the person must have a SQL Server or Windows NT login account. As with fixed server roles, any member of a fixed database role can add other logins to that role. Each member gains the permissions applied to the fixed database role. Table 9-2 shows the nine fixed database roles that are available in which users can be added and deleted in SQL Server 7.0.

Fixed Database Role Description
db_owner Can perform the activities of all database roles, as well as other maintenance and configuration activities in the database.
db_accessadmin Can add or remove Windows NT groups, Windows NT users, and SQL Server users in the database.
db_datareader Can see any data from all user tables in the database.
db_datawriter Can add, change, or delete data from all user tables in the database.
db_ddladmin Can add, modify, or drop objects in the database.
db_securityadmin Can manage roles and members of SQL Server database roles, and can manage statement and object permissions in the database.
db_backupoperator Can back up the database.
db_denydatareader Cannot see any data in the database, but can make schema changes.
db_denydatawriter Cannot change any data in the database.

Table 2: Descriptions for the Nine Fixed Database Roles in SQL Server 7.0

There are two methods normally used to add users to a fixed database role. The first method is to use SQL Server Enterprise Manager. Depending on whether you are creating the login account or it already exists depends on where you add a user to a fixed database role. If you are creating the login, then you use the Database Access tab to designate the database role allowed for the login account. You can also use SQL Server Enterprise Manager if the login account already exists. In this case you highlight Database Roles from a database in SQL Server, Next, in the right pane, right-click on the role to which you want to add a user or users. Select Properties from the menu, and then click the Add button as shown in Figure 9-9.

Figure 9: Adding a User to the db_owner Database Role

The second method is to use the sp_addrolemember system stored procedure in a Transact-SQL statement. The only users allowed to execute sp_addrolemember are members of the db_owner fixed database role. The sp_addrolemember system stored procedure accepts two arguments: role name and member name. The following example adds the SQL Server user Susan to the db_accessadmin role in the current database.

EXEC sp_addrolemember 'db_accessadmin', 'Susan'

The syntax is not very different if you need to add a Windows NT user to a fixed database role. The following example adds the Windows NT user Henry, who belongs to the SDC domain, to the db_datareader role in the current database.

EXEC sp_addrolemember 'db_datareader', 'SDC\Henry'

Public

There is one other fixed database role that we have not discussed yet. The public role is a special database role to which every database user belongs. The public role is different from the other fixed database roles we discussed in the following ways:

User-Defined Database

Using Windows NT groups is an excellent method to control users in regards to the access they have to your database. What do you do, however, if you have a group of users that needs to perform a specified set of activities in SQL Server 7.0 and you do not have permissions to manage Windows NT groups? You cannot add a fixed server role, but you can add a user-defined database role in the applicable database.

There are two types of user-defined database roles: Standard Role and Applications Role. Standard Role includes members, which we will discuss first. Let’s look at an example of where a user-defined database role fits in the needs of your organization. Imagine that your company forms a committee to support an upcoming charity telethon. The members of the committee are from each department that exists in the company. Each of the committee members needs to access a table in one of your databases. This is in order to complete their work supporting the telethon by contacting people in each of the company’s territories. Odds are there is not an existing Windows NT group that includes just the members of the committee, and there really is no other reason to create one. Instead of creating a new group in Windows NT, you can create a user-defined database role named Telethon_Committee. After creating the user-defined role, you simply need to add the committee members login accounts, either in SQL Server or Windows NT. Permissions given in the user-defined database role do not affect any other permission for other database activities.

Exam Watch: User-defined database roles exist within a database and cannot span more than one database.

There are several advantages to using user-defined database roles. Some of these are:

There are two methods normally used to create a user-defined database role. The first method uses the SQL Server Enterprise Manager. Once you define the database role, it appears in the Database Roles for the database as shown in Figure 9-10.

fig 9-10.gif (18507 bytes)

Figure 10: Adding the Telethon_Committee User-Defined Database Role to a Database

Now it is time for you to practice creating a user-defined database role using the SQL Server Enterprise Manager. You will accomplish this by completing Exercise 9-2. The exercise assumes that you have the Northwind database installed on your SQL Server.

Exercise 9-2: Adding a User-Defined Database Role to a Database

  1. Click the Start button and select Programs | Microsoft SQL Server 7.0 | Enterprise Manager.
  2. Select a SQL Server Group and click the + to expand it.
  3. Select a SQL Server and click the + to expand it.
  4. Select Databases and click the + to expand it.
  5. Select Northwind and click the + to expand it.
  6. Right click on Database Roles and select New Database Role… from the menu.
  7. Type Telethon_Committee in the Name: dialog box.
  8. Click the Add… button and select a name to add to the Telethon_Committee database role.
  9. Click the OK button.
  10. Double click on Telethon_Committee in the right pane of Database Roles.
  11. Click the Permissions button.
  12. Scroll down the list until you reach the Territories object.
  13. Place a check mark in the SELECT box for the Territories object.
  14. Click the OK button. The users added to the Telethon_Committee user-defined database role are now able to access the Territories table.

The second method normally used to create user-defined database roles is the sp_addrole system stored procedure in a Transact-SQL statement. To execute sp_addrole, you must be a member of one of the following: the sysadmin fixed server role, the db_securityadmin. or the db_owner fixed database role. Rolename and ownername are the two arguments supported by the sp_addrole system stored procedure. If ownername is omitted, then it defaults to dbo. If an ownername is specified, then it must be a user or role in the current database. The following example shows the role Telethon_Committee being added to the current database with an owner of dbo.

EXEC sp_addrole ‘Telethon_Committee’

Application

In the previous section we discussed creating user-defined database roles that included users in the Standard Role. The other type of user-defined database role that can be created is called the Application Role. The Application Role allows you to protect a database with a password.

SQL Server implements security at the lowest level within the database. This is the soundest method for controlling user activities no matter what application they use to communicate with SQL Server. There may be times that you need to customize security controls to accommodate the special requirements of an individual application. You may also want to restrict users to only being able to access data through a specific application that is provided to them. You may not want them to access directly using the SQL Server Query Analyzer or any other application.

Several of the differences between the Standard Role and the Application Role of the user-defined database role are:

There are two methods normally used to create an Application Role. The first method is to use SQL Server Enterprise Manager. Basically it is the same as creating a Standard Role as seen in the previous section. The only exception is that you choose the Application Role radio button and provide a password in the Password: dialog box as shown in Figure 9-11.

Figure 11: Creating an Application Role User-Defined Database Role

The second method available to create an Application Role is to use the sp_addapprole system stored procedure in a Transact-SQL statement. The only people that can execute sp_addapprole are members of the db_owner and db_securityadmin fixed database roles. The arguments available to sp_addapprole are rolename and password. The password is stored in an encrypted state. The following example adds the new Application Role Restricted_App to the current database with the password 24iLTgBOFn62.

EXEC sp_addapprole 'Restricted_App', '24iLTgBOFn62'

No matter whether you use SQL Server Enterprise Manager or a Transact-SQL statement to create the Application Role, it must be activated or else it is not used. This is accomplished only through the use of the sp_setapprole system stored procedure. Transact-SQL must be used to execute sp_setapprole. Any user can execute sp_setapprole as long as they know the correct password that corresponds to the Application Role they wan to activate. To provide security, the password can be hardcoded in the application you distribute to your users. Rolename, password, and encrypt are the three main arguments available to sp_setapprole. The following example activates an application role named Marketing_App with the clear text password 10s24d62c.

EXEC sp_setapprole 'Marketing_App', '10s24d62c'

The next example activates the Engineering_App application role with the password cmagicbil. This examples also encrypts the password before sending it to SQL Server. N converts the password to Unicode and must be used with the Encrypt keyword. Odbc specifies that the password is encrypted using the ODBC canonical Encrypt function prior to being sent to SQL Server 7.0. This option cannot be used by DB-Library clients. Only ODBC clients can use this option.

EXEC sp_setapprole 'Engineering_App', {Encrypt N 'cmagicbil'}, 'odbc'

Now that you have seen the different methods of creating and assigning SQL Server roles, here is a quick reference for possible scenario questions with the appropriate answer:

"Management has formed a team from different offices to temporarily study specific portions of the sales database…" Create a Standard user-defined database role that can access the specific portions of the sales database and place the members of the team in it.
"Jesse needs to be able to manage logins for the SQL Server…" Add Jesse to the securityadmin fixed server role.
"Mike wants to have the database he is responsible for accessed only with a password…"

Create an Application user-defined database role and distribute an application that has the password hardcoded in it.

"Franklin needs to be able to backup a single database …" Add Franklin to the db_backup operator fixed database role.
"David needs the capability to perform any activity on the company SQL Server…" Add David to the sysadmin fixed server role.

Implementing Permissions

When a user successfully connects to SQL Server, it only completes half of the mission. The activities they can perform are determined by the other half of the mission. These permissions are granted to either their user account, Windows NT group, or the role hierarchies to which their user account belongs. To perform any activity in SQL Server, the user must have the appropriate permissions.

There are three types of user permissions that we will discuss in this section:

After we examine user permissions, we will discuss server-to-server permissions that you may encounter in your SQL Server environment.

Object Permissions

Owners of objects must grant permissions to users to allow them to access their objects. Object permissions are based on a table, view, or stored procedure. They control the ability for a user to execute the SELECT, INSERT, UPDATE, and DELETE statements against the table, view, or stored procedure. Object permissions are changed using SQL Server Enterprise Manager, or through using a Transact-SQL statement. Let’s look at using the SQL Server Enterprise Manager first. Figure 9-12 shows permissions for the Invoices object. The user marissa has been granted SELECT and INSERT permissions for that object.

Figure 12: Viewing Permissions for the Invoices Object

Now is time for an exercise so that you can practice setting permissions on a database object. Exercise 9-3 assumes that you have the Northwind database installed on your SQL Server.

Exercise 9-3 Changing Permissions on a Database Object

  1. Click the Start button and select Programs | Microsoft SQL Server 7.0 | Enterprise Manager.
  2. Select a SQL Server Group and click the + to expand it.
  3. Select a SQL Server and click the + to expand it.
  4. Select Databases and click the + to expand it.
  5. Select Northwind and click the + to expand it.
  6. Click Stored Procedures.
  7. Right click on the Invoices object located in the right pane. Highlight Task and select Manage Permissions.
  8. Make sure that the radio button to the left of List All Users/DB Roles is selected.
  9. Place a check mark in the SELECT box for the user guest.
  10. Click the OK button. The user guest can now SELECT the Invoices object.

You may need to grant permissions on multiple objects to a user, group, or role. SQL Server Enterprise Manager can help you easily accomplish this feat. Expand the database you need to modify and select either Database Users or Database Roles. This is dependent on whether you want to modify a user, group, or role. In the details pane, right click the user, group, or role and select Properties from the menu. Click Permissions and place a check mark on the object to which you wish to give permission. Figure 9-13 shows permissions granted to the Administrators group for several different objects.

Figure 13: Granting Several Object Permissions to the Administrators Group

Object permissions can also be modified using GRANT in a Transact-SQL statement. Several different SQL Server members can use GRANT. Object owners can grant permissions for the objects they own. The db_owner role can grant any permissions on any object in the database it owns. Members of the sysadmin and db_securityadmin roles can grant any permission in any database. The following example grants the INSERT, UPDATE and DELETE permissions to the users Deena, Chris, and Dan on the Employees table.

GRANT INSERT, UPDATE, DELETE
ON Employees
TO Deena, Chris, Dan

Statement Permissions

You may need to allow a user to create a table within a database. This capability is outside the scope of object permissions. Therefore, another type of permission is available to fill this void. Statement permissions such as CREATE TABLE, are available and applied to the statement itself instead of a specific object in the database. There are several statement permissions available in SQL Server 7.0. These include CREATE TABLE, CREATE DATABASE, CREATE PROCEDURE, and CREATE RULE.

Statement permissions can be added using SQL Server Enterprise Manager or Transact-SQL. To add statement permissions to users in a database using SQL Server Enterprise Manager, right-click on a database and choose Properties from the menu. Click the Permissions menu, and select the statement permission you wish to grant to each user. Figure 9-14 shows the CREATE TABLE and CREATE VIEW statement permissions being granted to user marissa in the Northwind database.

Figure 14: Granting Statement Permissions in the Northwind Database to a User

Remember how we used GRANT from within Transact-SQL earlier in the chapter to assign object permissions? GRANT can also be used to assign statement permissions. The following example grants the CREATE DATABASE and CREATE TABLE statement permissions to the users DW and DJ.

GRANT CREATE DATABASE, CREATE TABLE
TO DW, DJ

Implied Permissions

Members of predefined system roles, or owners of database objects, are the only ones capable of implied permissions. For example, members that belong to the sysadmin fixed server role automatically inherit full permission to do anything in SQL Server. The implied permissions of owners of database objects also have implied permissions. These permissions allow them to perform all activities with the objects they own. For example, a user who owns a table can do anything they want with that table such as viewing, adding or deleting data. The user can also control permissions of other users allowed to work with the table.

Enabling Access to Remote Data

SQL Server has the capability to allow access from other SQL Servers that are remotely located using Remote Procedure Calls (RPC). You can use the SQL Server Enterprise Manager to grant permissions which allow remote SQL Servers the capability to login to your SQL Server. Right click on the server you want to allow connection into, and choose Properties. Select the Connection tab. Place a check mark in the first box to connect remotely to this SQL Server via RPC (see Figure 9-15).

Figure 15: Enabling other SQL Servers the Capability to Connect to the P233 SQL Server

Linked Servers

If you need to execute commands on different OLE DB data sources, then you can configure SQL Server to support linked servers. Linked servers offer several advantages including remote server access and the capability to issue distributed queries, updates, commands, and transactions. When a client application executes a distributed query through a linked server, SQL Server breaks down the query and sends the requests to OLE DB.

Setting Up Security for Linked Databases

When SQL Server 7.0 processes a distributed query, SQL Server may have to provide a login name and password on behalf of the current SQL Server login. Mappings at a linked server level map the current login on the sending server to a remote login on the remote server. There are two methods of creating a linked server login to SQL Server. The first method uses SQL Server Enterprise Manager (as shown in Figure 9-16) by opening a server and clicking on Linked Servers. Impersonate is used so that the local login connects to the linked server using their own user security credentials. If the user’s local login security credentials are not going to be used, then you need to enter the remote user and remote password for the linked server.

Figure 16: Configuring Security for a Linked Server

The second method uses the sp_addlinkedsrvlogin system stored procedure in a Transact-SQL statement. The sp_addlinkedsrvlogin system stored procedure can be executed only by members of the sysadmin and securityadmin fixed server roles. The following example creates a mapping to ensure that all logins to the local server connect. These logins must connect through the linked server Finances using their own user security credentials.

EXEC sp_addlinkedsrvlogin 'Finances'

Obtaining Information on Permissions

If you need to obtain information on permissions located on your SQL Server, you use the sp_helprotect system stored procedure from a Transact-SQL statement. The sp_helprotect system stored procedure returns a report that includes information about user permissions for an object, or statement permissions in the current database. The syntax for sp_helprotect includes several arguments such as name, username, grantorname, and permissionarea. All of the arguments are optional. If no arguments are given, then sp_helprotect displays all of the permissions that have been granted or denied in the current database. If you use some, but not all of the arguments, then you must use named parameters to identify the particular parameter, or use NULL as a placeholder. For example, to report all permissions for the grantor dbo, you could execute either of the following Transact-SQL statements.

EXEC sp_helprotect NULL, NULL, dbo
EXEC sp_helprotect @grantorname = 'dbo'

Let’s look at a few other examples of sp_helprotect. The next example lists the permissions for the books table in the current database.

EXEC sp_helprotect ‘books’

The previous example lists all of the statement permissions in the current database, using NULL as a placeholder for the missing parameters. Notice that ‘s’ is used to signify statement permissions. We could use ‘o’ if we want to retrieve object permissions.

EXEC sp_helprotect NULL, NULL, NULL, ‘s’

Resolving Broken Ownership Chains

An ownership chain is defined as when one item depends on another item. For example, a view may depend on tables or other views. Normally, the owner of a view also owns all of the subordinate objects, such as other views or tables. SQL Server 7.0 does not check permissions on any of the view’s subordinate objects if these objects and the view are all owned by the same user and included in the same database.

If more than one user owns portions of a procedure or view then the ownership chain is broken. When the ownership chain is broken, SQL Server checks permissions on each object in the chain whose next lower link is owned by a different user. By having SQL Server check the permissions, it allows the owner of the original data to retain control over who is authorized to access it.

A user who creates a view or stored procedure that depends on an object owned by another user must be aware of certain situations. Any permissions they grant depend entirely on the permissions that the other owner allows. Let’s look at an example to help clear up any confusion over broken ownership chains. Sharon creates a procedure named Pone, which depends on Ptwo and Pthree. Sharon owns Ptwo, and Pthree is owned by Marie. Sharon grants Jeff permission to use Pone. SQL Server checks the permissions on Pone and Pthree, to check that Jeff is allowed to use them. Ptwo was not checked because it is owned by Sharon, the creator of Pone. If Jeff cannot access Pone, then the permissions assigned by Marie on Pthree need to be checked.

Certification Summary

SQL Server 7.0 contains flexible authentication methods to meet a variety of situations. SQL Server can be configured to use Mixed mode authentication, which allows users to login with a SQL Server login, or transparently using their Windows NT security credentials. SQL Server can also be configured to only support Windows NT authentication.

SQL Server supports fixed server roles, fixed database roles, and user-defined database roles. Using roles makes it easy to manage the permissions for your login accounts. It is much easier for you to move users between roles rather than having to manage the permissions for each individual user. When you use a user-defined database, and the function of the job changes, certain modifications can be made. You can modify the permissions for the role once and have the changes automatically adjusted to all members for that role.

Databases can be controlled to a very granular level by correctly applying permissions. We discussed three types of user permissions; object, statement, and implied. Other permissions available in SQL Server allow it to be remotely accessed by other SQL Servers using Remote Procedure Calls. It is also possible to link servers so that distributed queries can be accomplished. A report can be accomplished to identify permissions by using the sp_helprotect system stored procedure in a Transact-SQL statement.

An ownership chain exists when one item depends on another item. If the same user does not own the subordinate objects, then the ownership chain is broken. You must be very careful in this situation. If you create a view or procedure that depends on an object owned by another user, then the permissions of the other owner affects the permissions you can grant.

Two-Minute Drill