SQL Server 2000 C2 Administrator's and User's Security Guide

Updated : July 19, 2001

Welcome
Chapter 1 - SQL Server Security Overview
Chapter2 - Installation of the Evaluated Configuration
Chapter3 - Auditing
Chapter4 - Administrative Tools and Practices
Chapter 5 - Using SQL Server Security
Appendix - Unsupported Stored Procedures and Tables
Tables to Drop

Welcome to the Microsoft® SQL Server™ C2Administrator's and User's Security Guide. This book is designed to help you install and use a C2-level certified SQL Server. The base version is SQL Server 2000.

This book has the following chapters:

Chapter 1, "SQL Server Security Overview," discusses the SQL Server 2000 product, as well as the security architecture.

Chapter 2, "Installation of the Evaluated Configuration," discusses installation of the evaluated configuration, including account policies.

Chapter 3, "Auditing," discusses auditing management, policies, problems, and solutions.

Chapter 4, "Administrative Tools and Practices," discusses the graphical tools used to administer SQL Server on Microsoft Windows NT® 4.0 servers and clients, administrative warnings, and how to deal with malicious code.

Chapter 5, "Using SQL Server Security," discusses security mechanisms, logging on, and warnings.

Appendix, "Unsupported Stored Procedures and Tables," lists the stored procedures and tables that must be deleted to create an evaluated configuration.

This guide assumes that you have the product documentation for SQL Server version 2000 (SQL Server Books Online, Sql80.col). You will also need the Microsoft Windows NT 4.0 C2 Administrator's and User's Security Guide (available at https://www.microsoft.com/security) and other documents referenced in it.

Computer security refers to the protection of all components - hardware, software, and stored data - of a computer or a group of computers from damage, theft, or unauthorized use. A computer security plan that is well thought out, implemented, and monitored makes authorized computer use easy and unauthorized use or accidental damage increasingly difficult.

This chapter is written primarily for Microsoft SQL Server system administrators. However, there are some issues that end users need to be aware of as well.

Some Definitions

C2 security is a government rating for security in which the system has been certified for discretionary resource protection and auditing capability.

SQL Server system administrators perform activities related to SQL Server installation, managing servers and clients, performance monitoring, security issues, and the backup and restore of databases. System administrators should be made members of the sysadmin fixed server role, and log on using their own Windows Authentication logins (user accounts).

A Microsoft Windows NT 4.0 user account represents a single, human user. Information included within a user account includes user name, full name, description, password, logon hours, logon workstations, expiration date, home directory, profile, associated groups, account type, and account condition (for example, disabled and password never expires). User account information is kept on the domain controller and the backup domain controller.

For information on the variety of SQL Server products, see "SQL Server Editions" in SQL Server Books Online (SQL80.col).

No SQL Server service packs are available for SQL Server 2000 at this time.

The evaluated configuration of SQL Server requires Windows Authentication to provide security. Windows Authentication is integrated with the Windows NT 4.0 security system and includes features such as secure validation and encryption of passwords, auditing, password expiration, minimum password length, and account lockout after multiple invalid login requests.

SQL Server login security is achieved when a user who is using Windows NT 4.0 identification and authentication is identified and approved. A user's network security attributes are established at network login time and are validated through a sophisticated password encryption mechanism. When a network user tries to connect, SQL Server uses Windows NT 4.0-based facilities to determine the validated network username. SQL Server then permits or denies login access based on that network username alone, without requiring a separate login name and password.

Login security integration operates over network protocols that support authenticated connections between clients and servers. Such connections are referred to as trusted connections and are supported by SQL Server using any available network library.

When a user connects through a Windows NT 4.0 user account, SQL Server verifies that the account name and password were validated when the user logged on to Windows NT 4.0.

The SQL Server system administrator specifies all the Windows NT 4.0 accounts that can connect to SQL Server. Login accounts are created, disabled, or deleted within Windows NT 4.0 by an authorized administrator through the User Manager or User Manager for Domains utilities.

Because SQL Server receives the Windows NT 4.0 user's system identification (SID) only at connection time, any revocation of the user's rights (that is, revocation of permissions in the master.dbo.sysxlogins table) does not affect the user's access to SQL Server until the next time a connection is made.

For more information, see "Using SQL Server Security" in this guide.

The evaluated configuration of SQL Server runs on a C2-evaluated Windows NT 4.0 operating system with Service Pack 6a, plus the post-Service Pack 6a hotfix "C2 Update," identified in the C2 Administrator's and User's Security Guide for Windows NT. SQL Server relies on the operating system to function correctly, to enforce its security policy, and to provide SQL Server with services and resources to implement the data base management system (DBMS) and its objects. The following figure illustrates the relationship of the operating system to the DBMS:

Figure 1: Composite Trusted Computing Base

The evaluated configuration for SQL Server 2000 includes SQL Server installed on any number of Windows NT 4.0 Servers. The Windows NT 4.0 Server may act in any one of the following roles, connected through a network consisting of zero or more Windows NT 4.0 domains:

• Microsoft Windows NT 4.0 Server product

• Primary Domain Controller (PDC)

• Backup Domain Controller (BDC)

• Non-Domain Controller (domain member)

• Non-Domain Controller (non-domain member)

• Domain member

• Non-domain member

It's important that the Windows NT 4.0 system be properly configured and maintained to support the evaluated configuration of SQL Server, as described in the Windows NT C2 Administrator's and User's Security Guide. An invalid configuration of Windows NT 4.0 will invalidate the installation of SQL Server.

SQL Server consists of two trusted services, SQL Server and SQL Server Agent. SQL Server contains the main DBMS executable to perform database and security functions. SQL Server Agent runs as a separate executable to provide administrative support such as event monitoring and batch job scheduling. SQL Server Agent is discussed in more detail in "Domain User Account Requirements" in this guide.

Network Infrastructure for the Evaluated Configuration

The evaluated configuration assumes that the physical network infrastructure (for example, Ethernet) is protected and controlled by a single security administrative authority. This assumption is driven by the fact that a C2 evaluation does not generally address cryptography and other means of protection against unauthorized individuals that are able to gain physical access to the network media. Instead, the assumed scope of the C2 homogeneous network evaluation is the "system security architecture;" in other words, the assessment of the ability of the SQL Server security architecture to protect resources from inappropriate access through the untrusted user and programming interfaces provided by each SQL Server product running on a Windows NT 4.0 system node on the network.

Only trusted users (SQL Server sysadmins) may log in to SQL Server on the machine where SQL Server 2000 is installed in the evaluated configuration. In addition, the administrator must ensure that no untrusted services or user-writable shares are available on the server where SQL Server 2000 is installed. This prevents a user from attempting to bind to the SQL Server listener port.

Replicated and Distributed Databases in the Evaluated Configuration

The evaluated configuration supports replicated databases. Replication permits copies of whole or partial databases to exist on multiple SQL Server installations. However, only transactional replication is supported in the evaluated configuration.

System tables are not replicated across the databases; therefore data definition language (DDL) changes on a publisher databases must be made manually at the subscriber databases. All security changes must also be made directly to the subscription database servers.

For more information about replication in general and transactional replication specifically, see SQL Server Books Online (SQL80.col).

The evaluated configuration does not support distributed databases. That is, data cannot be combined from different SQL Server installations to form a single database.

Components Not Included in the Evaluation

Some of the common SQL Server components that are not part of the evaluated configuration include:

• Full-text search

• Distributed Transaction Coordinator

• SQL Mail

• User-installed extended stored procedures

• Microsoft Meta Data Services, including its stored procedures (procedures stored in the msdb database). For more information, see "Appendix, Unsupported Stored Procedures."

• Microsoft Analysis Services

• Microsoft English Query

The Adminstrators tools that are available in the evaluated configuration are listed in "Administrative Tools and Practices," later in this guide.

The evaluated configuration of Microsoft® SQL Server™ includes most features of SQL Server 2000. (Omitted and included features are listed in Chapter 1, "SQL Server Security Overview" in this guide.) This section describes the Microsoft Windows NT® 4.0 accounts you'll need to proceed and the steps you'll need to complete the installation and configuration of an evaluated configuration.

This information is useful to know before installing and configuring the evaluated SQL Server.

SQL Server and SQL Server Agent are started as Windows NT 4.0 services named MSSQLServer for a default instance or MSSQL$Instancename for a named instance and SQLServerAgent.These services must be assigned a Windows NT 4.0 domain user account. Both services should be assigned to the same account.

You must be an administrator of the computer to set up SQL Server, but the service accounts for MSSQLServer or MSSQL$Instancename and SQLServerAgent services should not be local Windows NT 4.0 administrators.

Because the evaluated configuration cannot be installed under the administrator or system accounts, use This account, instead, during the configuration process (described later in this section). This account enables you to specify in which Windows NT 4.0 domain account the MSSQLServer or MSSQL$Instancename and SQLServerAgent services run. Also, a user account needs to be created before the installation. The user must be a member only of the users group (not administrators). Deny this user account the interactive logon right.

Domain User Account Requirements

The Windows NT 4.0 account that you specify for evaluated configuration must be a member of the sysadmin role on the local server running SQL Server. (The steps for adding a user account to the sysadmin role are described later in this section under "Configuring SQL Server.")

The following rights are automatically given to the service account selected during configuration:

• Act as part of the operating system.

• Increase quotas.

• Replace a process-level token.

• Log on as a service.

Once a domain user account is specified to run the SQL Server services, this account will have permission to:

• Access and change the SQL Server directory (\MSSQL).

• Access and change the .mdf, .ndf, and .ldf database files.

• Read and write registry keys at and under:

• HKEY_LOCAL_MACHINE \Software \Microsoft \MSSQLServer.

• HKEY_LOCAL_MACHINE \System \CurrentControlset \Services \MSSQLServer.

• HKEY_LOCAL_MACHINE \Software \Microsoft \Windows NT\CurrentVersion\Perflib.

Installing Internet Explorer 5.0

Microsoft Internet Explorer version 5.0 is required to proceed with the installation of SQL Server. Choose the minimal install with the web browser and Visual Basic scripting support. For more information, refer to the Internet Explorer page at the Microsoft Web Site. https://www.microsoft.com/isapi/redir.dll?Prd=IE&Pver=5.0&Ar=home

Depending on the product you have chosen, it may be labeled Enterprise Edition, Standard Edition, or Personal Edition. If you want the desktop version, install the Personal Edition. For a complete description of these editions and other available editions, see SQL Server Setup/Upgrade Help, described next.

In this section you're going to review the SQL Server Setup/Upgrade Help file (and print it if desired), and then continue with the SQL Server Setup program:

1. From the SQL Server 2000 CD or network location, run Autorun.exe.

2. Click Browse Setup/Upgrade Help.

3. Review the setup instructions for SQL Server 2000. You may want to print sections of the Help for step-by-step instructions.

4. Return to the SQL Server 2000 screen, and click Install SQL Server 2000 Components.

5. Click Database server to start the setup program.

6. Select the appropriate evaluated computer, Local computer or Remote computer.

7. Click Create a new instance of SQL Server.

8. Enter your name and organization.

9. Click Yes to accept the licensing agreement.

10. Click Server and Client Tools, or Client Tools Only, as appropriate.

11. Enter an instance name, or accept the Default if available.

12. In the Setup Type screen, select Custom Setup.

    In the Select Components screen, make sure only the following components are selected:

    • For Server Components

    • SQL Server

    • Replication Support

    • For Management Tools

    • Enterprise Manager

    • Profiler

    • Query Analyzer

    • Conflict Viewer

    • For Client Connectivity (no sub-components)

    • For Books Online

    • Books Online on Disk

13. Clear any other components or sub-components if they are selected.

14. Specify an account for the SQL Server and Agent to run under. Pick an account that is only a member of the Users group. Do not select the administrator.

15. Click Windows Authentication Mode.

16. Click Named Pipes and TCIP/IP Sockets, and complete Setup.

After installing SQL Server 2000, make the following configuration changes:

1. From the SQL Server 2000 CD or network location, run Autorun.exe.

2. Add the Service account for the MSSQLServer or MSSQLServer$Instancename and SQLServerAgent services as members of the sysadmin fixed-server role. You do this through Enterprise Manager or Transact-SQL. For instructions, see "Adding a Member to a Predefined Role" in SQL Server Books Online.

3. Add the Windows NT 4.0 accounts of SQL Server Administrators as members of the sysadmin fixed server role. You do this through Enterprise Manager or Transact-SQL. For instructions, see "Adding a Member to a Predefined Role" in SQL Server Books Online.

4. Remove the BUILTIN\Administrators Login rights from SQL Server. You do this through Enterprise Manager or Transact-SQL. For instructions, see "Removing Logins and Users." To execute this Transact-SQL command from Query Analyzer, enter the following:

   exec sp_revokelogin [BUILTIN\Administrators]

5. Delete (or don't use) all the referenced stored procedures and tables in "Appendix, Unsupported Stored Procedures and Tables." This will remove Microsoft Meta Data Services and replication features that are not included in the evaluated configuration. Instructions for deleting these procedures appear in the Appendix.

6. Disable the data transformation services by opening Control Panel, selecting Services, selecting MSDTC, and clicking Stop. Then click Startup and click Disabled mode.

7. Add login rights and database access rights, as needed, for end users. For instructions, see "Adding a SQL Server Login" and "Granting a SQL Server Login Access to a Database" in SQL Server Books Online.

8. Delete the following registry key value by typing regedit from the Run dialog box, which is available on the Start menu. (The Windows NT C2 configuration deletes this value, but sometimes during the SQL Server/Internet Explorer installation, it is added to the Registry again.)

   Hive: HKEY_LOCAL_MACHINE \SYSTEM

   Key: \CurrentControlSet\Control\Session Manager\Environment

   Value Name: Os2LibPath

To keep SQL Server in its evaluated state, it is important to do the following:

• Don't leave the Windows NT 4.0 evaluated configuration.

• Don't add any components that are not part of the evaluated configuration.

• Don't give the service accounts administrative rights on the server running the evaluated Windows NT 4.0 and SQL Server.

Auditing is a new feature in Microsoft® SQL Server™ 2000 that provides an audit trail of activity that has occurred on SQL Server. Enabling C2 auditing automatically sets all auditable events and collects them in the audit log. Using auditing and enabling C2 auditing is optional in an evaluated configuration.

This C2 auditing capability gives a security administrator the ability to review who has logged in and who has failed in a login attempt, and to review both successful and unsuccessful use of permissions in accessing both statements and objects. This information can be used in a legal sense (to document activity), and in a technical sense (to look for security policy violations).

Because auditing must run when a user is not logged in, auditing is a built-in functionality of SQL Server. Thus, auditing is always on, but not in the context of any particular user.

Note: The Microsoft Windows NT® Service Control Manager does not notify SQL Server who started a service—just that the service is being started. Consequently, you must audit service control actions in Windows NT to get a complete audit trail of SQL Server activity.

Only members of the sysadmin fixed server role are able to control and modify auditing. Every modification of an audit is itself an auditable action.

The following auditable events are provided for C2 certification of SQL Server:

• End User Activity (for example, all SQL commands, logins, and logouts)

• DBA Activity (Data Definition Language statements other than Grant/Revoke/Deny and security events, Configuration [Database or Server])

• Security Events (Grant/Revoke/Deny, login/user/role add/remove/configure)

• Utility Events (Backup/Restore/ Bulk Insert/BCP/DBCC commands)

• Server Events (Shutdown, Pause, Start)

  Auditing registers shutdown operations if performed from within SQL Server. However, if the server is shutdown from the operating system or manually, the auditing event will not be traced except as a request from the operating system.

  The Windows NT 4.0 Service Control Manager does not notify SQL Server who started a service—just that the service is being started. Consequently, you must audit service control actions in Windows NT 4.0 to get a complete audit trail of SQL Server activity.

• Audit Events (Start Audit, Stop Audit)

  Each audit event record contains (at a minimum):

• Date and time of each event (start time of the event).

• Windows NT 4.0 Domain name of the user who caused the event to occur.

• Windows NT 4.0 UserName of the user who caused the event to occur.

• The Windows NT 4.0 Security ID (SID) of the user who caused the event to occur.

• Type of event (Event Class and Subclass).

• Success or failure of the event.

• Server name of the SQL Server.

• Origin of the request (Windows NT 4.0 client computer name).

• Name of the application the user is running.

• Server process id (SPID) of the user's SQL Server connection.

For more information on these events, see "Security Audit Event Category." For information on how to enable auditing, see the "Setting the Audit Trace Option," which follows, and "Auditing SQL Server Activity"in SQL Server Books Online.

The SQL trace feature supports file rollover. When auditing is enabled, the trace files roll over, creating a new file, when each reaches 200 megabytes (MB) in size. Once the new file is created, the old file handle is closed. For example, NewTrace.tdf, rolls over to NewTrace_1.tdf, which rolls over to NewTrace_2.tdf. There is no data loss when the logs are transferred. This rollover between files is completely automatic.

SQL Server will shut down when no more room is available to write audit logs in the \mmsql\data directory, where the files are located. If an unexpected system crash occurs during processing, the maximum number of audit records that could be lost is 128 kilobytes (KB) worth of data.

You set up C2 auditing and specify log file rollover with the C2 audit trace option, available as an advanced configuration setting. For more information, see "C2 audit mode Option" in "Setting Configuration Options" in SQL Server Books Online.

Use SQL Profiler to import the trace files into a SQL Server table, where they can be viewed, or view them directly in the SQL Profiler window. For more information, see "Monitoring With SQL Profiler" in SQL Server Books Online.

1. Start SQL Profiler.

2. From the File menu, click Open and select the .trc files generated from the audit.

3. Either view the trace in SQL Profiler, or save it in a SQL Server table. (Click Save As from the File menu.) If the trace is saved as a table, it can be analyzed through Transact-SQL commands.

You can also use FiletoTable.exe, a command line utility, to load a trace file to a database table so that data in the trace file can be analyzed through Transact-SQL commands.

Its parameters include:

/S <server name>

/U <user>

/P <password>

/E trusted connection

/d <database>

/t <table>

/i <input file>

Views depend on other tables or views. Stored procedures depend on tables, views, or other stored procedures. These two types of dependencies are called ownership chains. Views, stored procedures, and the objects they depend upon are frequently owned by the same user. In this case, the SQL Server does not make the discretionary access control (DAC) check on any of the underlying objects; only access to the view or stored procedure is checked.

In the case where another user owns an underlying object, the ownership chain is broken and SQL Server must make additional DAC checks. If the ownership chain of a stored procedure or view is broken, SQL Server checks permissions on each object in the chain whose next lower link is owned by a different user, and generates an audit record. No audit record is generated if the ownership chain is not broken.

To guarantee a complete audit trail, the administrator must maintain the audit trail indefinitely. To determine what objects were accessed as a result of executing a stored procedure or using a view, the administrator has several options. First, without any additional procedural means, the audit trail provides an account of all grant/revoke/deny access checks. All points where the owner decided to grant access is included in the audit trail. If the administrator wants to see all the underlying objects related to a stored procedure or view, the administrator must view the previous versions of the audit trail looking for an ALTER statement associated with the stored procedure or view. If the administrator finds an ALTER statement, then the administrator can read the text of the ALTER procedure or view and determine all the underlying objects. If no ALTER statement is found, then the administrator must continue until the create procedure or view is found to read the text of the ALTER procedure or view and determine all the underlying objects.

This section covers the graphical tools used for administering Microsoft ® SQL Server™ and other administrative practices and warnings.

The graphical tools that can be used for administering the evaluated configuration of SQL Server 2000 include the following:

• SQL Server Enterprise Manager

• SQL Query Analyzer

• Client Network Utility

• SQL Profiler

• Microsoft Windows NT® 4.0 Performance Monitor

For more information about these tools, see the "Overview of the SQL Server Tools" topic in SQL Server Books Online or start the individual tools and use their respective Help files.

In today's computing world, you must prevent intentional intrusions of malicious code into your network. These typically take the form of viruses and Trojan horses:

• Viruses are programs that attempt to spread from computer to computer and either cause damage (by erasing or corrupting data) or annoy users (by printing messages or altering what is displayed on the screen). For more information on viruses, see the Microsoft® Windows NT 4.0 Administrator's and User's Security Guide.

• Trojan horses are programs that masquerade as other common programs in an attempt to receive information. An example of a Trojan horse is a program that masquerades as a system logon screen to retrieve usernames and password information. The writers of the Trojan horse can use this information later to break into the system.

With Windows NT 4.0 security, the user must type the secure attention sequence, CTRL+ALT+DEL. This series of keystrokes always displays the Windows NT 4.0 operating system logon screen; it can never activate Trojan horse programs. Users are guaranteed to be providing their username and password only to the operating system itself.

In addition, for the evaluated configuration, administrators must only use the SQL Server evaluated administrative tools so that the server does not become susceptible to Trojan horses. For example, to stay compliant with certification, do not download customized versions of osql.

Follow these practices when performing administrative duties in the evaluated configuration of SQL Server:

• No linked or remote servers can be created because the only way to do this securely is with Kerberos on Windows 2000, which is not part of the evaluated configuration.

• Service accounts must be ordinary user accounts. This limits the privileges of the SQL Server with respect to the operating system.

• All backup and restore operations must be performed using disk or tape and not named pipes.

• The backup files are to be made accessible only to administrative accounts or groups, and their ownership should be changed to an administrative group.

• Do not grant permission to a user to restore a database other than the owner of the database. If someone other than the owner restores the database, the owner will no longer be able to access the data. This happens when user A owns a database and its tables, and grants permission to user B to backup and restore the database. If user B has CREATE authorization and backs up the database and then restores it to a new database, user B becomes the owner of the database but the ownership of the tables remain with user A. If the WITH DBO_ONLY option was specified on the RESTORE DATABASE statement, user A (the owner of the tables) is not authorized to access the tables in the restored database until user A is granted Discretionary Access Control (DAC) permission to the database.

• Do not install user-created extended stored procedures because they run full security rights on the server; thus, they can create a potential security problem. Also, user-created extended stored procedures are not part of the evaluated configuration.

• Do not install full-text search because it is not part of the evaluated configuration.

• Do disable Microsoft Distributed Transaction Coordinator, as no distributed transactions are allowed.

• Do not write audit information to the event log, as it is not protected from modification by untrusted users.

• Do not create or use any database application roles, as proper accountability cannot be assured.

• Do not enable access to the system tables for direct updating (such as done with DELETE, UPDATE, or INSERT). Make sure the allow updates option is set at its default of 0, which disables direct updating to the system tables by users, even if they have the appropriate permissions as assigned by the GRANT statement. (Users can still update system tables using system stored procedures only, but they should not do so.) For more information, see "allow updates Option" in SQL Server Books Online.

• Although the user's SQL Server username can differ from the Windows NT 4.0 username, Microsoft recommends that the two names be the same to facilitate audit trail correlation.

• If a user needs to be immediately removed from SQL Server, the administrator must shut down SQL Server.

This section supplements the security information available in Microsoft Windows NT C2 Administrator's and User's Guide and SQL Server Books Online.

The evaluated configuration of Microsoft® SQL Server™ uses Microsoft® Windows NT® 4.0 integrated security, which provides identification and authentication of users to control access to the system and enforce accountability, prevent access to residual information from a previous user's actions, and provide for the auditing of security related events. The SQL Server Discretionary Access Control (DAC) features provide protection for data internally, which is explained in the next section.

An untrusted user is one who is attempting a remote login to the local server. In the evaluated configuration, Windows Authentication protects against access by untrusted users. For the evaluated configuration of SQL Server, an untrusted user is any non-administrative user.

Client applications are untrusted when they are executing at the request of an authenticated user who is not a SQL Server administrator, and they are running from an account without special Windows NT 4.0 permissions, where the application is requesting services of SQL Server. Because the network connecting the client and the server is a closed network supported by evaluated components (Windows NT 4.0 and SQL Server with Windows Authentication), the connection between the client and the server is trusted.

In addition to Windows Authentication, logins, users, roles, and groups are the foundation for the security mechanisms of SQL Server 2000.

Users that connect to SQL Server must identify themselves using a specific login ID. Sp_grantlogin authorizes a Microsoft Windows® network account (either a group or a user account) to be used as a SQL Server login for connecting to SQL Server using Windows Authentication. For more information, see "Logins" in SQL Server Books Online.

Once logged on, a user can only see the tables and views that they are authorized to see, and can only execute the stored procedures and administrative functions they are authorized to execute. This system is based on the IDs used to identify users. A user ID identifies a user within a database. All permissions and ownership of objects in the database are controlled by the user account, which are specific to a database. For more information, see "Users" in SQL Server Books Online.

While there are no groups in SQL Server 2000, Window NT 4.0 groups can be used in much the same way as roles. For more information, see "Groups" in SQL Server Books Online.

Roles can be used to organize users where an equivalent Windows NT 4.0 group does not exist. Roles allow you to collect users into a single unit against which you can apply permissions. Permissions granted to, denied to, or revoked from a role also apply to any members of the role. For example, you can establish a role that represents a job performed by a class of workers in your organization and grant the appropriate permissions to that role. Application roles are not part of the evaluated configuration. For more information, see "Roles" in SQL Server Books Online.

The syspermissions table contains information about permissions that have been granted and denied to users, groups, and roles in the database. It is stored in each database.

There are three ways a user can gain access to an object. The first is by being the object owner. The second is to be granted access to the object, and third is to have access based upon database role membership. The next section discusses the specific types of permissions and how each permission fits into SQL Server's overall DAC policy.

Permissions

When users connect to Microsoft SQL Server, the activities they can perform are determined by the permissions granted to their Windows NT 4.0 groups or role hierarchies to which their security accounts belong. The user must have the appropriate permissions to perform any activity that involves changing the database definition or accessing data. Some activities, such as those that do not change items in the database or access data, do not require permissions. For more information on permissions, see "Managing Permissions" in SQL Server Books Online.

Granting and Revoking Access

To obtain access to a Microsoft SQL Server database, a Microsoft Windows NT 4.0 user or group must have a corresponding user account in each database they need to access, and to which permissions are applied. For information on granting SQL Server permissions, see "Granting Permissions," "Roles," "How to grant a Windows NT 4.0 user or group access to a database (Enterprise Manager), (SQL DMO), or (Transact-SQL)" in SQL Server Books Online.

Note: Auditing always reflects the User's ID, not the group, even if database access was obtained through group membership.

Because Windows NT 4.0 users and groups are maintained only by Windows NT 4.0, SQL Server reads information about a user's membership in groups when the user connects. If changes are made to the accessibility rights of a connected user, the changes become effective the next time the user connects to SQL Server or logs on to Windows NT 4.0 (depending on the type of change).

Likewise, changing access on an object does not affect any current access, but will be enforced for future attempts to obtain access to the object.

For more information, see "How to Revoke a Windows NT 4.0 user or group login access from SQL Server (Enterprise Manager), (SQL-DMO), or (Transact-SQL)" in SQL Server Books Online.

You can log in and log off to an instance of Microsoft SQL Server from any of the graphical administration tools or from a command prompt, using osql. When logging in to an instance of SQL Server using a graphical administration tool such as SQL Server Enterprise Manager or SQL Query Analyzer, you are prompted to supply the server name and a password, if necessary. (For the evaluated configuration, which requires Windows Authentication, you do not have to provide a login ID each time you access a registered SQL Server. Instead, SQL Server logs you in automatically using your Microsoft Windows NT 4.0 account.)

Always press CTRL+ALT+DEL before logging on to Windows NT 4.0 and to SQL Server. Programs designed to collect account passwords can appear as a standard logon screen. By pressing CTRL+ALT+DEL you can foil these programs and get the secure logon screen provided by Windows NT 4.0.

Either log off or lock the workstation if you will be away from the computer for any length of time. Logging off allows other users to log on (if they know the password to an account); locking the workstation does not.

For more information, see "Logging In to SQL Server" in SQL Server Books Online.

Changing a Password

Anyone who knows a Windows NT 4.0 login (username and the associated password) can log on as that user and gain access to SQL Server. Here are a few tips for keeping passwords secure:

• Change passwords frequently, and avoid reusing passwords.

• Avoid using easily guessed words and words that appear in the dictionary. A phrase or a combination of letters and numbers works well.

• Don't write a password down—choose one that is easy for you to remember.

To change your password, press CTRL+ALT+DELETE, and then click Change Password.

When you change a Windows NT 4.0 password, however, you will have to change the password for MSSQLServer and MSSQL$Instancename and SQLServerAgent services. For more information, see "Troubleshooting MSSQLServer or SQLServerAgent Services User Accounts" in SQL Server Books Online.

The following stored procedures and tables are not supported in the evaluated configuration and must be removed from (or not used in) the Microsoft® SQL Server™ installation. Deleting them will remove Microsoft Meta Data Services and replication features that are not included in the evaluated configuration.

You can use the Query Analyzer to execute the drop statement. For more information, see "Executing SQL Statements in SQL Query Analyzer" in SQL Server Books Online.

To delete the following stored procedures, use this Transact-SQL statement:

DROP PROCEDURE {procedure) [ ,|.n]

Meta Data Services Stored Procedures

r_iDbmCatalog

r_iDbmColumn

r_iDbmColumnSet

r_iDbmConnection

r_iDbmDataSource

r_iDbmDataType

r_iDbmIndex

r_iDbmMiscellaneous

r_iDbmPackage

r_iDbmProvider

r_iDbmProviderDataType

r_iDbmProviderTypeMapping

r_iDbmReferentialRole

r_iDbmTable

r_iDbmUmlOperation

r_iDtmNamespace

r_iDtmObjectType

r_iDtmObjectTypeMapping

r_iDtsDPTaskAssignment

r_iDtsMQMessage

r_iDtsNode

r_iDtsOLEDBProperty

r_iDtsStepExecution

r_iDtsTransformationStep2

r_iEqmAnswer

r_iEqmDatabaseConnection

r_iEqmDefaultCondition

r_iEqmEntity

r_iEqmFieldRef

r_iEqmModel

r_iEqmQuestion

r_iEqmRegression

r_iEqmRelationship

r_iGenElement

r_iGenMiscellaneous

r_iIFMNVarChar

r_iIFMVarChar

r_iIFXFragment

r_iMdsCommon

r_iMdsDatasource

r_iMdsDimension

r_iMdsLevel

r_iMdsMiningModel

r_iMdsRole

r_iMdsServer

r_iMdsStore

r_iOclSequence

r_iOlpDimension

r_iOlpDimHierarchy

r_iRTblClassDefs

r_iRTblEnumerationDef

r_iRTblEnumerationValueDef

r_iRTblIfaceDefs

r_iRTblIfaceMem

r_iRTblNamedObj

r_iRTblParameterDef

r_iRTblPropDefs

r_iRTblProps

r_iRTblRelColDefs

r_iRTblRelshipDefs

r_iRTblRelshipProps

r_iRTblRelships

r_iRTblScriptDefs

r_iRTblSites

r_iRTblSumInfo

r_iRTblTypeInfo

r_iRTblTypeLibs

r_iRTblVersionAdminInfo

r_iRTblVersions

r_iRTblWorkspaceItems

r_iSimDependency

r_iSimDimensionRef

r_iSimEntity

r_iSimLevelRef

r_iSimMeasureRef

r_iSimMember

r_iSimMemberPropertyRef

r_iSimMiscellaneous

r_iSimPhrasing

r_iSimReadSynonym

r_iSimRef

r_iSimRelationship

r_iSimType

r_iSimWord

r_iSimWriteSynonym

r_iTfmCodeDecodeValue

r_iTfmDependency

r_iTfmElement

r_iTfmPackage

r_iTfmStepExecution

r_iTfmTransformation

r_iTfmTransformationTask

r_iUmlAssociationRole

r_iUmlAttribute

r_iUmlElement

r_iUmlGeneralizableElement

r_iUmlMember

r_iUmlMethod

r_iUmlNote

r_iUmlOperation

r_iUmlpackage

r_iUmlParameter

r_iUmlPoint

r_iUmlProjection

r_iUmlReference

r_iUmlSignalReference

r_iUmlStereotype

r_iUmlTaggedValue

r_iUmlType

r_iUmlValue

r_iUmxMiscellaneous

Meta Data Services Base Stored Procedures

r_iRTblClassDefs

r_iRTblClassDefs

r_iRTblEnumerationDef

r_iRTblEnumerationValueDef

r_iRTblIfaceDefs

r_iRTblIfaceMem

r_iRTblNamedObj

r_iRTblParameterDef

r_iRTblPropDefs

r_iRTblProps

r_iRTblRelColDefs

r_iRTblRelshipDefs

r_iRTblRelshipProps

r_iRTblRelships

r_iRTblScriptDefs

r_iRTblSites

r_iRTblSumInfo

r_iRTblTypeInfo

r_iRTblTypeLibs

r_iRTblVersionAdminInfo

r_iRTblVersions

r_iRTblWorkspaceItems

Meta Data Services Information Model Stored Procedures

r_iRTblCDEProps

r_iRTblCOMProps

r_iRTblDBMProps

r_iRTblDBXProps

r_iRTblDTMProps

r_iRTblDTSProps

r_iRTblEQMProps

r_iRTblGENProps

r_iRTblMDSProps

r_iRTblOLPProps

r_iRTblSIMProps

r_iRTblTFMProps

r_iRTblUMLProps

r_iRTblUMXProps

sp_addmergealternatepublisher

sp_addmergearticle

sp_addmergefilter

sp_addmergepublication

sp_addmergesubscription

sp_addtabletocontents

sp_changemergearticle

sp_changemergefilter

sp_changemergepublication

sp_changemergesubscription

sp_deletemergeconflictrow

sp_dropmergealternatepublisher

sp_dropmergearticle

sp_dropmergefilter

sp_dropmergepublication

sp_dropmergesubscription

sp_enumcustomresolvers

sp_generatefilters

sp_getmergedeletetype

sp_helpallowmerge_publication

sp_helpmergealternatepublisher

sp_helpmergearticle

sp_helpmergearticlecolumn

sp_helpmergearticleconflicts

sp_helpmergeconflictrows

sp_helpmergedeleteconflictrows

sp_helpmergefilter

sp_helpmergepublication

sp_helpmergesubscription

sp_mergearticlecolumn

sp_mergecleanupmetadata

sp_mergedummyupdate

sp_reinitmergesubscription

sp_showrowreplicainfo

sp_validatemergepublication

sp_validatemergesubscription

Undocumented Stored Procedures for merge replication (sp_MS*)

sp_MSadd_mergereplcommand

sp_MSaddguidcolumn

sp_MSaddguidindex

sp_MSaddinitialarticle

sp_MSaddinitialpublication

sp_MSaddinitialschemaarticle

sp_MSaddinitialsubscription

sp_MSaddmergedynamicsnapshotjob

sp_MSaddmergepub_snapshot

sp_MSaddmergeschemaarticle

sp_MSaddmergetriggers

sp_MSaddpubtocontents

sp_MSaddupdatetrigger

sp_MSadjustmergeidentity

sp_MSalreadyhavegeneration

sp_MSbelongs

sp_MSchangearticleresolver

sp_MSchangemergeschemaarticle

sp_MSchangeobjectowner

sp_MScheckatpublisher

sp_MScheckexistsgeneration

sp_MScheckidentityrange

sp_MSCheckmergereplication

sp_MScheckmetadatamatch

sp_MSchecksnapshotstatus

sp_MScleanup_conflict

sp_MScleanup_conflict_table

sp_MScleanup_metadata

sp_MScleanuptask

sp_MSclearcolumnbit

sp_MScomputemergearticlescreationorder

sp_MScomputemergeunresolvedrefs

sp_MScontractsubsnb

sp_MScreatebeforetable

sp_MScreateglobalreplica

sp_MScreateretry

sp_MSdeletepushagent

sp_MSdeleteretry

sp_MSdelgenzero

sp_MSdelrow

sp_MSdelsubrows

sp_MSdelsubrowsbatch

sp_MSdrop_expired_mergesubscription

sp_MSdrop_rladmin

sp_MSdrop_rlcore

sp_MSdrop_rlrecon

sp_MSdroparticletombstones

sp_MSdropconstraints

sp_MSdropdynsnapshotvws

sp_MSdropmergedynamicsnapshotjob

sp_MSdropmergepub_snapshot

sp_MSdropretry

sp_MSdroptemptable

sp_MSdummyupdate

sp_MSenumchanges

sp_MSenumdeletesmetadata

sp_MSenumgenerations

sp_MSenumpartialchanges

sp_MSenumpartialdeletes

sp_MSenumpubreferences

sp_MSenumreplicas

sp_MSenumretries

sp_MSenumschemachange

sp_MSenumschemachange_70

sp_MSenumschemachange_80

sp_MSevalsubscriberinfo

sp_MSexpandbelongs

sp_MSexpandnotbelongs

sp_MSexpandsubsnb

sp_MSfetchidentityrange

sp_MSfillupmissingcols

sp_MSfixlineageversions

sp_MSgentablenickname

sp_MSget_subtypedatasrc

sp_MSgetbeforetableinsert

sp_MSgetchangecount

sp_MSgetcolumnlist

sp_MSgetconflictinsertproc

sp_MSgetconflicttablename

sp_MSgetlastrecgen

sp_MSgetlastsentgen

sp_MSgetlastsentrecgens

sp_MSgetmetadatabatch

sp_MSgetonerow

sp_MSgetpubinfo

sp_MSgetrowmetadata

sp_MSgetsubscriberinfo

sp_MSgettablecontents

sp_MSgetviewcolumnlist

sp_MShelpalterbeforetable

sp_MShelpcreatebeforetable

sp_MShelpdestowner

sp_MShelpmergearticles

sp_MShelpmergeconflictcounts

sp_MShelpmergeconflictpublications

sp_MShelpmergedynamicsnapshotjob

sp_MShelpmergeidentity

sp_MShelpmergeschemaarticles

sp_MShelpvalidationdate

sp_MSindexcolfrombin

sp_MSinitdynamicsubscriber

sp_MSinsertdeleteconflict

sp_MSinserterrorlineage

sp_MSinsertgenerationschemachanges

sp_MSinsertgenhistory

sp_MSinsertschemachange

sp_MSlocalizeinterruptedgenerations

sp_MSmakearticleprocs

sp_MSmakeconflictinsertproc

sp_MSmakectsview

sp_MSmakedynsnapshotvws

sp_MSmakeexpandproc

sp_MSmakegeneration

sp_MSmakeinsertproc

sp_MSmakejoinfilter

sp_MSmakeselectproc

sp_MSmakesystableviews

sp_MSmaketempinsertproc

sp_MSmakeupdateproc

sp_MSmakeviewproc

sp_MSmaptype

sp_MSmergepublishdb

sp_MSprepare_mergearticle

sp_MSproxiedmetadata

sp_MSpublicationview

sp_MSquerysubtype

sp_MSrefcnt

sp_MSreinit_hub

sp_MSreinitmergepublication

sp_MSremove_mergereplcommand

sp_MSremove_userscript

sp_MSreplcheck_permission

sp_MSretrieve_mergepublication

sp_MSscript_dri

sp_MSsetartprocs

sp_MSsetconflictscript

sp_MSsetconflicttable

sp_MSsetlastrecgen

sp_MSsetlastsentgen

sp_MSsetreplicainfo

sp_MSsetreplicastatus

sp_MSsetrowmetadata

sp_MSsetsubscriberinfo

sp_MSsetupbelongs

sp_MSsetupbelongs_withoutviewproc

sp_MSsetupnotbelongs

sp_MSsetupworktables

sp_MSsubscriptionvalidated

sp_MSsubsetpublication

sp_MStablenamefromnick

sp_MStablenickname

sp_MSuniquecolname

sp_MSuniqueobjectname

sp_MSuniquetempname

sp_MSupdategenhistory

sp_MSupdateschemachange

sp_MSupdatesysmergearticles

sp_MSuplineageversion

sp_MSvalidatearticle

To delete the following tables, use this Transact-SQL statement:

DROP TABLE_table_name

There are no foreign key relationships between the Meta Data Services tables, so the tables can be dropped in any order.

Meta Data Services Base Tables

ITblClassExtension

RTblClassDefs

RTblDatabaseVersion

RTblEnumerationDef

RTblEnumerationValueDef

RTblIfaceDefs

RTblIfaceHier

RTblIfaceMem

RTblNamedObj

RTblParameterDef

RTblPropDefs

RTblProps

RTblRelColDefs

RTblRelshipDefs

RTblRelshipProps

RTblRelships

RTblScriptDefs

RTblSites

RTblSumInfo

RTblTypeInfo

RTblTypeLibs

RTblVersionAdminInfo

RTblVersions

RTblWorkspaceItems

Meta Data Services Information Model Tables

RTblCDEProps

RTblCOMProps

RTblDBMProps

RTblDBXProps

RTblDTMProps

RTblDTSProps

RTblEQMProps

RTblGENProps

RTblMDSProps

RTblOLPProps

RTblSIMProps

RTblTFMProps

RTblUMLProps

RTblUMXProps

Many of these tables don't exist until a merge publication has been created:

MSmerge_agents

MSmerge_altsyncpartners

MSmerge_contents

MSmerge_delete_conflicts

MSmerge_errorlineage

MSmerge_genhistory

MSmerge_history

MSmerge_perfcounters

MSmerge_replinfo

MSmerge_subscriptions

MSmerge_tombstone

sysmergearticles

sysmergeschemaarticles

sysmergeschemachange

sysmergesubscriptions

sysmergesubsetfilters

Information in this document is subject to change without notice. Companies, names, and data used in examples herein are fictitious unless otherwise noted. No part of this document may be reproduced or transmitted in any form or by any means, electronic or mechanical, for any purpose, without the express written permission of Microsoft Corporation.

Microsoft Corporation may have patents or pending patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. The furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property rights except as expressly provided in any written license agreement from Microsoft Corporation.

© 1985-2000 Microsoft Corporation. All rights reserved.

Microsoft, Windows, and MS-DOS are registered trademarks and Windows NT is a trademark of Microsoft Corporation in the United States of America and other countries.

Adaptec is a trademark of Adaptec, Inc.

AppleTalk is a trademark of Apple Computers, Inc.

Compaq, Qvision, and SmartStart are registered trademarks and ProLiant is a trademark of Compaq Computer Corporation.

Alpha, AXP, DEC, and Digital are trademarks of Digital Equipment Corporation.

Hewlett-Packard, HP, and LaserJet are registered trademarks of Hewlett-Packard Company.

Intel and Pentium are registered trademarks and i386 is a trademark of Intel Corporation.

NT is a trademark of Northern Telecom.

OS/2 is a registered trademark of International Business Machines Corporation.

SCSI is a registered trademark of Security Control Systems, Inc.

UNIX is a registered trademark in the United States and other countries, licensed exclusively through X/Open Company, Ltd.