12.      Security

SQL Server has an internal security mechanism that offers sophisticated layers of control to all objects within a database.

Microsoft have provided an integrated security mode which integrates security with NT security. This involves the creation of two NT user groups called SQLAdmins and SQLUsers and the maintenance of SQL Server security using the SQL Security Manager application.

This handbook originally described integrated security. However from a developers point of view, security is simpler if controlled using SQL Server standard security and consequently discussion in this basic overview is limited to SQL Server security.

Security is specified in the Server Configuration window.

Standard Security

Server Logins

The SQL Server application requires Logins to be defined for each type of user that requires access to the database server. Logins are global to the Server and are mapped to individual users specified for each database.

Specifying a New Login

Server Logins are global to each server and are the user identifier and password that are used to log onto the server with the ODBC datasource.

Database Users

Users are defined for each Database and determine access to the database. The creator of the database is defined as the DBO user and has all privileges on the database.

Additional users can be created from the SQL Executive by selecting the Database and expanding the outline for the Groups/Users option and righclicking to select the New Users option.

Creating a new User for a Database

Each Database User is mapped to a Server Login when adding the user so that SQL Server can automatically determine the Database User when a Server Login logs onto the system. The Database User and Server Login can be given the same names to avoid confusion.

The Aliased Logins mover in the Manage Database Users window  may be used to add several Server Logins to an individual Database User.

Permissions

A database initially has permissions set for the DBC Database User who has all available permissions and for the Public user group who have the default permissions.

The permissions for the PUBLIC group can be revoked using the Object Permissions window available in the SQL Enterprise Manager. To revoke all permissions for PUBLIC, select the BY USER Page and select the PUBLIC Group and then press the REVOKE ALL button followed by SET to remove any permissions from the Public group.

Permissions for a specific user may be granted with the Grant All button. The Object Permissions window can be used to select all Tables, Views, or Stored Procedures and grant and revoke permissions accordingly.

Setting Permissions for a User

Individual Tables can be selected for each user and SELECT, INSERT, UPDATE and DELETE permissions applied. Each field on each table may also have Select and Update permissions individually applied per User.

Stored Procedures may have EXECUTE permissions granted or revoked to allow only certain users to run them.

Database Groups

A Database Group may be defined to define overall security for a Group of Database Users. The individual permission differences for a specific user may then be granted or revoked for the individual user.

Creating a Database Group