Setting Up Integrated Security

In integrated login security mode, SQL Server uses Windows NT authentication mechanisms for all connections. Only trusted (multi-protocol or named pipes) connections are allowed. For more information about the integrated login security mode, see Chapter 8, Security Concepts."

The following procedure summarizes the tasks performed to set up integrated security. Only the system administrator can perform steps 1 and 3. Only a Windows NT administrator, domain administrator, or account operator can accomplish step 2. Steps 4 and 5 can be performed by either the system administrator or the database owner.

    To set up integrated security
  1. Set the server's security options.

    Use SQL Enterprise Manager to set the login security mode to Integrated. Also set the other security options: Default Login, Default Domain, Set HostName to Username, Audit Level, and Mappings.

    For information on setting these options, see Setting the Server Security Options.

  2. Create the Windows NT groups and users authorized to access SQL Server over trusted (multi-protocol and named pipes) connections.

    Use Windows NT User Manager. If you have not been granted access to User Manager to administer Windows NT groups and users, have a Windows NT Administrator perform this task for you. For more information, see Managing Access for Windows NT Groups and Users.

  3. Authorize selected Windows NT-based groups and users to access SQL Server.

    Use SQL Security Manager to map Windows NT groups and users to SQL Server login IDs. You will decide whether each user will use a separate SQL Server login ID or will access SQL Server through the default login ID. You will also choose a default database for the users. For more information, see Managing Access for Windows NT Groups and Users.

    Note that the setup program, by default, grants SA access for the local Windows NT Administrators group on the server.

  4. Set object permissions for each database.

    Use SQL Enterprise Manager to grantūto users or groupsūselect, insert, update, or delete permission for tables and views; reference permissions for tables; and execute permission for stored procedures. You can optionally specify column-level permissions for tables and views. For more information, see Managing Object Permissions.

  5. Set statement permissions for each database.

    Use SQL Enterprise Manager to assign permissions that allow users or groups to perform various database tasks, such as creating rules and defaults on a database, creating tables and views on a database, creating stored procedures, and backing up a database. For more information, see Managing Statement Permissions.

The following illustration depicts the tasks performed to set up integrated security.