SQL Server supports two authentication modes: Windows mode and mixed mode. In Windows mode, when a user connects through a Windows user account, SQL Server validates the account name and password by using information from the operating system. In mixed mode, in addition to Windows authentication, a user can provide a SQL login and password to connect to SQL Server. SQL Server can use and enforce Windows password policy mechanisms.

SQL Server defines two fundamental terms for security: principals and securables. Principals are entities that can request SQL Server resources. They are arranged in a hierarchy in the principal's scope: you can have Windows, server, and database-level principals. A principal can be a Windows domain login, a Windows local login, a Windows group, a SQL Server login, a server role, a database user, a database role, or an application role in a database. In addition to having regular users, you can create a SQL Server login or a database user from a certificate or an asymmetric key.

Securables are the resources you are protecting. Some securables can be contained within others in nested hierarchies (scopes). You can secure a complete scope, and the objects in the scope inherit permissions from the upper level of the hierarchy. The securable scopes are server, database, and schema.

After authentication, in the authorization phase, SQL Server checks whether a principal has appropriate permissions to use the securables. The following figure shows the most important principals, securables, and permissions in SQL Server, including the level where they are defined:

You manage principals with Data Definition Language (DDL) statements. You maintain permissions with Data Control Language (DCL) statements. You create a principal as you do any other objects, by using the CREATE statement. You modify them by using the ALTER statement and delete them by using the DROP statement.

You can create SQL Server logins, which are security principals, or you can create logins from different sources, such as from Windows, certificates, or asymmetric keys. When you create SQL Server logins, you can specify that you want to bypass password expiration and account policies. However, these policies help to secure your system, for example, preventing brute-force password attacks, and therefore this option is not recommended.

Database users are still part of the authentication. SQL Server supports two models: the traditional login and user model and the contained database user model, described as follows:

• In the traditional model, a login is created in the master database and then mapped to a user in some other database. The end user connects to SQL Server with a login, and, through the mapping to one or more databases, the user gets access to the database(s).
• In the contained model, a database user is either mapped to a Windows user directly or one is created with a password. The end user connects to a single database directly, without having to log in to the master database.

The following code shows how to create a SQL Server login with a weak password. If you execute this code, you get an error because the password does not meet Window's password policy requirements:

USE master; 
CREATE LOGIN LoginA WITH password='LoginA'; 
GO 

However, the following code succeeds. It creates a SQL Server login with a weak password, this time bypassing the Windows password policy, and creates a login from a built-in Windows group:

CREATE LOGIN LoginA WITH password='LoginA', 
CHECK_POLICY=OFF; 
CREATE LOGIN [BuiltinPower Users] FROM WINDOWS; 

Bypassing password expiration and complexity policies is definitely not recommended. The SQL Server login just created is now very prone to brute-force attacks. You can check the sys.sql_logins catalog view to see which SQL logins do not enforce the policies mentioned, as the following code shows:

SELECT name,  
type_desc,  
is_disabled,  
is_policy_checked,  
is_expiration_checked 
FROM sys.sql_logins 
WHERE name LIKE 'L%'; 

The result shows the login that was just created:

name    type_desc   is_disabled is_policy_checked is_expiration_checked
------ ----------   -----------  -------------     -------------------  
LoginA    SQL_LOGIN   0           0                 0

In SQL Server, you have some special principals. On the server level, you have the sa SQL Server login, which is created when you install SQL Server. The default database for this login is master. This login has all permissions on the server and you cannot revoke any permissions from this login. You should protect the sa login with a strong password. If you use Windows authentication only, this login cannot be used to connect to SQL Server.

In every database, you get the public fixed role and the guest user account. You cannot drop them. You can only disable the guest user account. Any login without a directly mapped user in a database can access the database through the guest account. Application roles can also use this account to access the data in databases other than the database in the context for which they were invoked. Before you give any permission to the guest user account, make sure you consider all the ramifications. Every database user and every database role is a member of the public role. Therefore, any user or role—including an application role—inherits all permissions given to the public role. You should be careful when giving any permission to the public role; the best practice is to never give any permission to it.

The privileged database user dbo still exists in SQL Server. This user is a member of the db_owner role and, therefore, has all permissions on the database. You cannot drop dbo from the db_owner role.

Every database includes two additional principals: INFORMATION_SCHEMA and sys. You cannot drop these principals because SQL Server needs them. They serve as schemas (namespaces) for ANSI-standard information schema views and for SQL Server catalog views. Finally, SQL Server provides some special logins based on certificates, where their name starts and ends with two hash characters, such as ##MS_dqs_db_owner_login##. These logins are for SQL Server internal use only.

The principals are securables by themselves. You can control who can modify logins using membership in the sysadmin and securityadmin server-level roles, and the ALTER ANY LOGIN server-level permission. You can control who can modify database users and roles by memberships in the db_owner and db_securityadmin roles, and the ALTER ANY USER and ALTER ANY ROLE permissions.

In SQL Server, object metadata is not visible to the public role (that is, everyone) by default. You can control metadata visibility by using two permissions: VIEW ANY DATABASE and VIEW DEFINITION.

The VIEW ANY DATABASE permission is granted to the public role by default, so all logins can still see the list of all databases on a SQL Server instance unless you revoke this permission from the public role. You can check this server-level permission by querying the sys.server_permissions catalog view:

SELECT pr.name,  
pe.state_desc, 
pe.permission_name  
FROM sys.server_principals AS pr 
INNER JOIN sys.server_permissions AS pe 
ON pr.principal_id = pe.grantee_principal_id 
WHERE permission_name = 'VIEW ANY DATABASE'; 

The result of this query is as follows:

    name         state_desc permission_name
    ------       ---------- -----------------
    public       GRANT      VIEW ANY DATABASE

The VIEW DEFINITION permission lets a user see the definition of the securable for which this permission is granted. However, this permission does not give the user access to the securable; you have to give other permissions to the user if the user must work with database objects. If the user has any other permission on an object, the user can see the metadata of the object as well.