SQL Server Security: Logins

Logins provide access to SQL Server. As they are server-wide objects, they are stored in the Master database in the table sysxlogins. These logins can be Windows accounts, Windows groups, or if you are running in mixed mode, they can be SQL Server logins, which are referred to as standard logins.

When SQL Server is installed, two accounts are created: a Windows group called builtin\administrators that allows access to anyone in the Windows local administrators group, and a standard login called sa. These accounts are both members of the SQL Server role Sysadmin, which gives them full administration privilege on SQL Server and all databases.

NOTE

As both builtin\administrators and sa have full privileges on SQL Server, be careful in their use. Remember that the local administrators group contains the domain administrators group and anyone else who has been added. If it is not appropriate that they have access to SQL Server, you can remove the builtin\administrators login. The sa account can't be removed, but you should assign it a complex password and limit its use. It is preferred to create logins for users who will administer SQL Server and assign them to the sysadmins role. This provides greater accountability.

After SQL Server is installed, you can connect using the builtin\administrator or sa login to create additional Windows user and group logins as well as SQL Server logins, to provide access to the database users and administrative staff. After logins are created, they will have to be mapped to users to provide database access. A single login can be mapped to users in multiple databases.