Step 11. SQL Server Logins, Users, and Roles | Improving Web Application Security: Threats and Countermeasures

To be able to access objects in a database you need to pass two layers of security checks. First, you need to present a valid set of login credentials to SQL Server. If you use Windows authentication, you need to connect using a Windows account that has been granted a SQL Server login. If you use SQL Server authentication, you need to supply a valid user name and password combination.

The login grants you access to SQL Server. To access a database, the login must be associated with a database user inside the database you want to connect to. If the login is associated with a database user, the capabilities of the login inside the database are determined by the permissions associated with that user. If a login is not associated with a specific database user, the capabilities of the login are determined by the permissions granted to the public role in the database. All valid logins are associated with the public role, which is present in every database and cannot be deleted. By default, the public role within any database that you create is not granted any permissions.

Use the following recommendations to improve authorization settings in the database:

Use a strong sa (system administrator) password .
Remove the SQL guest user account .
Remove the BUILTIN\Administrators server login .
Do not grant permissions for the public role .

Use a Strong sa (System Administrator) Password

The default system administrator ( sa ) account has been a subject of countless attacks. It is the default member of the SQL Server administration fixed server role sysadmin . Make sure you use a strong password with this account.

Important

The sa account is still active even when you change from SQL authentication to Windows authentication.

Apply strong passwords to all accounts, particularly privileged accounts such as members of the sysadmin and db_owner roles. If you are using replication, also apply a strong password to the distributor_admin account that is used to establish connections to remote distributor servers.

Remove the SQL Guest User Account

When you install SQL Server, a guest user account is created if the Windows 2000 guest account is enabled. A login assumes the identity of guest if the login has access to SQL Server but does not have access to a database through a database user account.

It is a good idea to disable the Windows guest account. Additionally, remove the guest account from all user-defined databases. Note that you cannot remove guest from the master, tempdb, and replication and distribution databases.

Remove the BUILTIN\Administrators Server Login

By default, the BUILTIN\Administrators local Windows group is added to the sysadmin fixed server role to administer SQL Server. This means that domain administrators who are members of BUILTIN\Administrators have unrestricted access to the SQL Server database. Most companies differentiate the role of domain administrator and database administrator. If you do this , remove the BUILTIN\Administrators SQL Server login. It is a good idea to create a specific Windows group containing specific database administrations in its place and added to SQL server as a server login as shown in the following procedure.

Task To add a new login for database administrators

Start Enterprise Manager.
Expand Microsoft SQL Server , expand SQL Server Group , and then expand your SQL Server.
Expand the Security folder, select and right-click Logins , and then click New Login .
In the Name field, enter a custom Windows group that contains only database administrators.
Click the Server Roles tab, and then select System Administrators .

This adds the new login to the sysadmin server role.

Task To delete the BUILTIN\Administrators login

Start Enterprise Manager.
Expand Microsoft SQL Server , expand SQL Server Group , and then expand your SQL Server.
Expand the Security folder, and select Logins . If BUILTIN\Administrators appears in the list of logins, right-click it, and then click Delete to remove the login.

For more information about reconfiguring the SQL service accounts after the installation, see the MSDN article, "Changing Passwords and User Accounts" at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_afterinstall_4p0z.asp .

Do Not Grant Permissions for the Public Role

All databases contain a public database role. Every other user, group, and role is a member of the public role. You cannot remove members of the public role. Instead, do not grant the permissions for the public role that grant access to your application's database tables, stored procedures, and other objects. Otherwise, you cannot get the authorization that you want using user-defined database roles because the public role grants default permissions for users in a database.

Additional Considerations

Also consider the following recommendations when configuring SQL Server logins, users, and roles:

Limit the members of sysadmin . To make sure there is individual accountability, restrict the number of accounts that are members of the sysadmin role. Ideally, no more than two users are members of this role.
Grant restricted database permissions . Assign accounts only the absolute minimum permissions required to do a job. Avoid using the built-in roles, such as db_datareader and db_datawriter . These roles do not provide any authorization granularity and these roles have access to all of your custom database objects.
Do not change the default permissions that are applied to SQL Server objects . In versions of SQL Server earlier than Service Pack 3, the public role does have access to various default SQL Server database objects. With Service Pack 3, the security design has been reviewed and security has been improved by removing the public role where it is unnecessary and by applying more granular role checks.