Setting Up Standard Security

In standard login security mode, SQL Server manages its own login validation process for all connections. For more information about the standard login security mode, see Chapter 8, Security Concepts.

The following procedure summarizes the tasks performed to set up standard security. Only the system administrator can perform steps 1 and 2. Steps 3 through 5 can be performed by either the system administrator or the database owner.

    To set up standard security

1. Set the server's security options.

   Use SQL Enterprise Manager to verify that the login security mode of the server is Standard and to set the Audit Level options.

   Note that Standard is the default login security mode. For more information, see Setting the Server Security Options.

2. Allow user access to SQL Server.

   Use SQL Enterprise Manager to create a login ID and password, and specify a default language for each user. For more information, Managing SQL Server Logins.

3. Define database users.

   Use SQL Enterprise Manager to specify the databases that can be accessed by each login ID. For more information, see Managing Database Users.

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 standard security.