You configure access to SQL Server using server logins. You can configure various levels of access to these logins:
By the roles to which those logins belong.
By permitting access to specific databases.
By allowing or denying object permissions.
Just as there are two authentication modes, there are also two kinds of server logins. You create domain logins using domain accounts, which can be domain or local user accounts, local group accounts, or universal and global domain group accounts. You create SQL Server logins by specifying a unique login ID and password. Several logins are configured by default, and these include local Administrators, local Administrator, sa, NETWORK SERVICE, and SYSTEM.
To narrow the scope of access to a specific database, you use database user accounts. Several database users are configured by default including the dbo user (a special database user), the guest user (a special database user with limited access), the INFORMATION_SCHEMA user, and the sys user.
In this section, you will learn more about these special purpose logins.
The Administrators group is a local group on the database server. This group’s members normally include the local Administrator user account and any other users set to administer the system locally. In SQL Server, this group is granted the sysadmin server role by default.
Administrator is a local user account on the server. This account provides administrator privileges on the local system, and you use it primarily when you install a system. If the host computer is part of a Windows domain, the Administrator account usually has domain-wide privileges as well. In SQL Server, this account is granted the sysadmin server role by default.
The sa login is the system administrator’s account for SQL Server. With the new integrated and expanded security model, sa is no longer needed, and it is primarily provided for backward compatibility with previous SQL Server versions. As with other administrator logins, sa is granted the sysadmin server role by default. When you install SQL Server, the sa login is not assigned a password.
To prevent unauthorized access to the server, you should set a strong password for this account, and you should also change the password periodically as you would the passwords for Windows accounts.
Best Practices
Because the sa login is widely known to malicious users, you may want to delete or disable this account if possible. Instead of using the sa login, make System Administrators members of the sysadmin server role and have them log on using their own logins. Anyone with the sysadmin server role can then log on and administer the server. If you ever get locked out of the server, you can log on to the server locally using an account with local administrator privileges, and then reset passwords or assign privileges as necessary.
NETWORK SERVICE and SYSTEM are built-in local accounts on the server. Whether or not server logins are created for these accounts depends on the server configuration. For example, if you have configured the server as a Report Server, you will have a login for the NETWORK SERVICE account, and this login will be a member of the special database role RSExecRole on the master, msdb, ReportServer, and ReportServerTempDB databases. RSExecRole is used primarily to manage the Report Server schema, and the service account for the server instance will also be a member of this role.
During setup of the server instance, the NETWORK SERVICE and SYSTEM accounts can be the selected service account for SQL Server, SQL Server Agent, Analysis Services, and Report Server. In this case, the SYSTEM account will typically have the sysadmin server role, giving it full access for administration of the server instance.
The guest user is a special user that you can add to a database to allow anyone with a valid SQL Server login to access the database. Users who access a database with the guest account assume the identity of the guest user and inherit all the privileges and permissions of the guest account. For example, if you configure the domain account GOTEAM to access SQL Server, GOTEAM can access any database with a guest login, and when GOTEAM does so, that person is granted all the permissions of the guest account. If you were to configure the Windows group DEVGROUP with guest access, you could simplify administration since any user that is a member of the group would be able to access any database as a guest.
By default, the guest user exists in the model database and is granted guest permissions. Because model is the template for all databases you create, this means that all new databases will include the guest account, and this account will be granted guest permissions. You can add or delete guest from all databases except master and tempdb. Most users access master and tempdb as guests, and for this reason, you cannot remove the guest account from these databases. This is not a problem, however, because a guest has limited permissions and privileges in master and tempdb.
Before using the guest user, you should note the following information about the account:
The guest user is a member of the public server role and inherits the permissions of this role.
The guest user must exist in a database before anyone can access it as a guest.
The guest user is used only when a user account has access to SQL Server but does not have access to the database through this user account.
The database owner, or dbo, is a special type of database user and is granted special privileges. Generally speaking, the user who created a database is the database owner. The dbo is implicitly granted all permissions on the database and can grant these permissions to other users. Because members of the sysadmin server role are mapped automatically to the special user dbo, logins with the sysadmin role can perform any tasks that a dbo can perform.
Objects created in SQL Server databases also have owners. These owners are referred to as the database object owners. Objects created by a member of the sysadmin server role belong to the dbo user automatically. Objects created by users who are not members of the sysadmin server role belong to the user creating the object and must be qualified with the name of that user when other users reference them. For example, if GOTEAM is a member of the sysadmin server role and creates a table called Sales, Sales belongs to dbo and is qualified as dbo.Sales, or simply Sales. However, if GOTEAM is not a member of the sysadmin server role and creates a table called Sales, Sales belongs to GOTEAM and must be qualified as GOTEAM.Sales.
All system objects are contained in the schemas named sys or INFORMATION_ SCHEMA. These are two special schemas that are created in each database, but they are only visible in the master database. The related sys and information schema views provide an internal system view of the metadata for all data objects stored in a database. The sys and INFORMATION_SCHEMA users are used to reference into these views.