Client - Server Communication and Network Protocols

SQL Server Administration

An user is granted privileges to administer a server when he or she becomes a member of some administrator group. There are three administrator groups: CONFIG_ADMIN, DB_ADMIN and SECURITY_ADMIN.

Configuration Administrators Group CONFIG_ADMIN

The main task of a configuration administrator is to take care of SQL server operation, mainly by setting up runtime parameters and solving non-standard situations. Some of his competencies include:

Stopping SQL servers from then Server manager
Defining logs and inserting log tracing requests. (except for data tracing)
Reading the contents of all logs (basic log may be read by all users)
Setting up parameters for network communication, such as network protocol
Setting up IP addresses to allow or restrict network user access
Setting up folders for libraries containing extern functions, that can be called from the server
Enlarging and compressing the database file
Setting up parameters for periodic backup
Running integrity maintenance of the database file
Creating and deleting users and user groups
Enabling and disabling of separate user accounts
Forcing logoff of clients connected to a server or suspending client made actions on the server
Releasing locks placed on objects on the server by clients
Assigning user to the Author and Administrator roles in the _sysext application, so the user is allowed to e.g. edit system triggers
Setting up many other runtime parameters

Configuration administrator may gain another competencies, if the security administrator assigns them to the CONFIG_ADMIN group. It's mainly the privilege to assign users to or reject users from the CONFIG_ADMIN group.

E.g. system triggers _on_server_start, _on_server_stop, _on_backup etc. are executed with the CONFIG_ADMIN privileges.

Data Administrators Group DB_ADMIN

Data administrator has maximal access privileges to all data and each object in the database. Thus data administrator can read, write, update and delete all objects and records in the database.

Operation of database application doesn't usually require a data administrator, in some cases it's even against the concept of data privacy in certain applications. In the default state there won't be any data administrator on the server. His tasks are mainly overtaken by administrative roles due to different application needs. The purpose of existence of the data administrators group is ensuring compatibility with some older applications and providing a "backdoor" for solving nonstandard situations on the server.

Security Administrator Group SECURITY_ADMIN

The main task of a security administrator is to take essential decisions concerning the security of SQL server operation. Security administrator isn't involved in normal server operations.

In minor companies usually the owner or the manager becomes the security administrator, in major companies it's usually a person aside normal business who is responsible for security policies.

Competencies of a security administrator are:

Decision whether to set up a data administrator for the server, and assigning users to the DB_ADMIN group
Setting up tracing of data read and write.
Enabling and disabling anonymous user server access
Setting up then minimal length of user password and password expiration period
Decision whether to use "one-time-password" with a limited usage count
Decision whether encrypt database file
Choose a domain server from which to pick user logins, and decision about automatic account generation for the domain users
Manipulating with the secret key of the SQL server
Decision about encrypting network communication
Decision whether the configuration administrators can assign and reject users to/from the CONFIG_ADMIN group
Decision whether the configuration administrators can assign and reject users to/from the user groups
Changing a user password

List of topics:

SQL Server Operation

602SQL Server

Client - Server Communication and Network Protocols