Special Users of SQL Server

Three types of special users administer and control SQL Server: system administrators (SAs), database owners (DBOs), and database object owners.

• The system administrator (SA) is the person responsible for the administrative and operational functions that are independent of any particular application and is likely to have a comprehensive overview of SQL Server and all of its applications.

  The duties of an SA typically include installing SQL Server; configuring servers and clients; managing and monitoring the use of disk space, memory, and connections; creating devices and databases; authorizing SQL Server users and granting them permissions; transferring data in and out of SQL Server databases; backing up and restoring databases; implementing and maintaining replication; scheduling unattended operations; monitoring and tuning SQL Server performance; and diagnosing system problems. The system administrator may also advise application designers about the data that already exists on SQL Server, make recommendations about standardizing data definitions across applications, and so on.

  The SA operates outside the protection system; SQL Server does no permission checking for the SA. The SA is also treated as the owner of whatever database he or she is using. Anyone who knows the SA password can log in and act as SA.

• A database owner (DBO) is the user who creates a database. Each database has only one owner. The DBO has full privileges inside the database and determines the access and capabilities provided to other users. In the owned database, the user is recognized as DBO; in other databases, the database owner is known by his or her database username. DBO status can be reassigned to a different user. Only one login ID can be DBO, although other login IDs can be aliased to DBO. (An alias is a database username that is shared by several login IDs. An alias allows you to treat more than one person as the same user inside a database, giving all of them the same permissions.)

  The SA can be the DBO of some or all databases.

• A database object owner is the user who creates a database object (tables, indexes, views, defaults, triggers, rules and procedures). Each database object has only one owner. The database object owner is automatically granted all permissions on the database object. The database object owner can grant permission to other users to use that object. Database object ownership cannot be transferred.

Many of the commands and procedures discussed in this documentation require the user to be logged on as system administrator. (For information about logging on as SA, see Logging in and Logging Off.) Other topics discussed in this documentation are relevant to database owners and database object owners. No special login identification is needed for database or database object owners.

There are also three special login IDs¾probe, repl_publisher, and repl_subscriber¾that, depending on the server configuration, can exist by default on a server.

• When SQL Server is installed, the probe login ID is automatically created. This is a special login ID that is installed by the setup program and is used as the security context within which some administrative applications (for example, SQL Performance Monitor) connect to a SQL Server that is configured for standard security. Note that if a SQL Server is set up for integrated security, sa is used for this purpose, instead of probe.
• When a server is configured to participate in replication as a subscription server, the repl_publisher login ID is automatically created. It allows replication processes on the distributor (which may be a separate distribution server, or a combined publication/distribution server) to connect to the subscription server and replicate table schema and data to destination databases.
• When a server is configured to participate in replication as a publication server, the repl_subscriber login ID is automatically created. It allows replication processes on a subscription server to connect to the publication server and execute stored procedures.

The probe, repl_publisher and repl_subscriber login IDs should not be modified, nor should users use them to log in.

For in-depth information about system administrators, database owners, and database object owners, and the probe, repl_publisher and repl_subscriber login IDs, see Part 4, Security.