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 the Windows authentication, a user can provide a SQL login and password to connect to SQL Server. SQL Server can use and enforce the 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.

Principals, securables, and permissions

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 the 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: traditional login and user model and contained database user model.

The following code shows how to create an 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 an 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 like 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 via 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, the metadata of the objects is not visible to the public role (that is, everyone) by default. You can control the 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 an 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.

The complete name of a relational database management system (RDBMS) object consists of four parts. In SQL Server, the complete name form is server.database.schema.object. Objects also have owners, and owners are database users and roles. However, the owners are hidden; you typically never refer to an object owner in the code that deals with data, while you intensively use the schemas. Schemas are more than just namespaces for database objects; they are securables as well. Instead of giving permissions to specific database objects, a DBA can give users permissions to schemas. For example, granting the Execute permission to schema Sales gives the grantees the Execute permission on all objects in this schema for which this permission makes sense, such as stored procedures and functions. Therefore, you should plan your schemas carefully.

When you refer to database objects in your code, you should always use a two-part name, in form schema.object. You don't want to use more than two parts because you don't want to make your application dependent on a specific server or database name. However, because of the way SQL Server does the name resolution, you should not use a single-part name either.

In SQL Server, every user has a default schema. You can specify the default schema for a user when you create the user. You can change the default schema of a user at any later time. If you do not specify an explicit default schema for a user, the default schema is dbo. This schema exists in all SQL Server databases and is owned by the dbo user. Thus, SQL Server first checks for a partially specified object name if the object exists in the user's default schema and then checks the dbo schema. To fully understand this behavior, work through the following code; note that this code assumes you are working in the dbo database user context because it uses the EXECUTE AS command to impersonate a database user and you must have the correct permission to use this command.

The first part of the code creates a demo database and another login called LoginB. Note that the login called LoginA should already exist at this point:

USE master; 
IF DB_ID(N'SQLDevGuideDemoDb') IS NULL 
CREATE DATABASE SQLDevGuideDemoDb; 
CREATE LOGIN LoginB WITH password='LB_ComplexPassword'; 
GO 

The next part of the code creates a new schema called Sales in the SQLDevGuideDemoDb demo database, and then two tables with the same name and structure, one in the dbo schema and one in the new Sales schema:

USE SQLDevGuideDemoDb; 
GO 
CREATE SCHEMA Sales; 
GO 
CREATE TABLE dbo.Table1 
(id INT, tableContainer CHAR(5)); 
CREATE TABLE Sales.Table1 
(id INT, tableContainer CHAR(5)); 
GO

The following two insert statements insert one row into each table. The value of the character column shows the name of the table schema:

INSERT INTO dbo.Table1(id, tableContainer) 
VALUES(1,'dbo'); 
INSERT INTO Sales.Table1(id, tableContainer) 
VALUES(1,'Sales'); 
GO 

The next part of the code creates two database users, one for LoginA and one for LoginB, with the same name as their respective login name. Note that the default schema for user LoginA is dbo, while for LoginB it is Sales. Both users are also granted the permission to select the data from both demo tables:

CREATE USER LoginA FOR LOGIN LoginA; 
GO 
CREATE USER LoginB FOR LOGIN LoginB 
 WITH DEFAULT_SCHEMA = Sales; 
GO 
GRANT SELECT ON dbo.Table1 TO LoginA; 
GRANT SELECT ON Sales.Table1 TO LoginA; 
GRANT SELECT ON dbo.Table1 TO LoginB; 
GRANT SELECT ON Sales.Table1 TO LoginB; 
GO 

Next, you impersonate LoginA. In a query, you refer to the table you are reading with a single-part name only (that is, with table name only):

EXECUTE AS USER='LoginA'; 
SELECT USER_NAME() AS WhoAmI, 
id, 
tableContainer 
FROM Table1; 
REVERT; 
GO 

Here are the results:

WhoAmI  id  tableContainer
------  --  --------------
LoginA   1  dbo

You can see that you read from the dbo.Table1 table. Repeat the same thing for the database user LoginB:

EXECUTE AS USER='LoginB'; 
SELECT USER_NAME() AS WhoAmI, 
id, 
tableContainer 
FROM Table1; 
REVERT; 
GO 

This time the results show that you read the data from the Sale.Table1 table:

WhoAmI  id  tableContainer
------  --  --------------
LoginB   1  Sales

Now you drop the Sales.Table1 table. Then you impersonate the user LoginB again, and read from the table using the table name only:

DROP TABLE Sales.table1; 
GO 
EXECUTE AS USER='LoginB'; 
SELECT USER_NAME() AS WhoAmI, 
id, 
tableContainer 
FROM Table1; 
REVERT; 
GO 

Here are the results:

WhoAmI  id  tableContainer
------  --  --------------
LoginA   1  dbo

After gaining the knowledge of how schemas work, you should be able to understand the following guidelines for managing schemas:

All the demo code so far has supposed that you are authorized inside a database as the dbo user. This user has all possible permissions inside a database. However, in real life, it might be necessary for other users to create and modify objects. These users could be developers or other database administrators. To modify objects, they need statement permissions. Statement permissions are on the server, database, schema, or at object level, depending on which level you work at. In addition, end users must use objects, and thus need object permissions. Object permissions depend on the type of the object you are working with.

The statement permissions include permissions to use any Data Definition Language (DDL) statements (that is, to create, alter, and drop objects). The object permissions include permissions to use the objects (that is, to use the Data Modification Language (DML) statements). However, the two permissions' classes slightly overlap, and you can treat a couple of permissions as both statement and object permissions.

You control permissions by using the Data Control Language (DCL) elements: the GRANT, REVOKE, and DENY statements. You already know that without explicitly granted permission, a user cannot use an object. You give the permissions by using the GRANT statement. You explicitly prohibit the usage of an object by using the DENY statement. You clear an explicit GRANT or an explicit DENY permission by using the REVOKE statement. You might wonder why you need an explicit DENY statement when, without an explicit GRANT, a user cannot use an object. The DENY statement exists because all grants are cumulative. For example, if a user gets a GRANT permission to select from table1 and the role that the user is a member of is granted permission to select from table2, the user can select from both tables. If you want to be sure that the user can never select from table2, you should deny the select permission from table2 to this user. A DENY for an ordinary user always supersedes all GRANTs.

You cannot grant, deny, or revoke permissions to or from special roles at the server or database level. For example, you cannot deny anything inside a database to the db_owner role. You cannot grant, deny, or revoke permissions to special logins and database users (that is, to sa, dbo, INFORMATION_SCHEMA, and sys). Finally, you cannot grant, deny, or revoke permissions to yourself.

Statement permissions let users create and alter objects, or back up a database and transaction log. Permissions granted on a higher level include implicit permissions on a lower level. For example, permissions granted at the schema level are implicitly granted on all objects in the schema. In addition, there is a hierarchy between permissions on the same level; some are stronger and implicitly include weaker permissions. The CONTROL permission is the strongest. For example, the CONTROL permission on the database level implies all other permissions on the same database. Therefore, you have two different kinds of hierarchy: hierarchy between securables and hierarchy between permissions. You can treat high-level permissions as covering the more detailed, low-level permissions that they imply. For example, if a user needs to alter an object, the user needs either the ALTER OBJECT permission or any other higher permission, such as the ALTER ANY SCHEMA permission.

The types of permissions depend on the types of database objects. You can get a list of permissions applicable for an object or objects by using the sys.fn_builtin_permissions system function. For example, you can check which permissions are applicable for user-defined types, or check the objects for which the SELECT permission is applicable, like the following two queries do:

SELECT * FROM sys.fn_builtin_permissions(N'TYPE'); 
SELECT * FROM sys.fn_builtin_permissions(DEFAULT)  
WHERE permission_name = N'SELECT'; 
GO 

In SQL Server, you can specify very detailed permissions. For example, you can specify that a user can select or update only some columns of a table. Specifying permissions on such a granular level means a lot of administrative work, and is nearly impossible to do in a limited time with graphical tools such as SQL Server Management Studio. You should rarely go that far.

The GRANT statement includes the WITH GRANT OPTION. This option indicates that the principal to whom you grant permission on an object can grant this permission on the same object to other principals.

The DENY statement comes with the CASCADE option. When you use this option with the DENY statement, you indicate that the permission you are denying is also denied to other principals to which it has been granted by this principal.

The REVOKE statement has the GRANT OPTION FOR and the CACSCADE options. GRANT OPTION FOR means you are revoking permission to grant the same permission to other principals (that is, you are revoking the WITH GRANT OPTION permission you gave to this principal by using the GRANT statement). The CASCADE option means you are revoking permission not just from the principal you mention in the statement but also from other principals to which permission has been granted by this principal. Note that such a cascading revocation revokes both the GRANT and DENY of that permission.

The following code shows how to use the object permissions. First, the code grants the CONTROL permission on dbo.Table1 to LoginB. LoginB can read the table.

GRANT CONTROL ON dbo.Table1 TO LoginB; 
GO 
EXECUTE AS USER = 'LoginB'; 
SELECT * 
FROM dbo.Table1; 
REVERT; 
GO 

Next, you deny the SELECT permission on dbo.Table1 to LoginB. Note that LoginB still has the CONTROL permission on this table, so this user can insert into the table.

DENY SELECT ON dbo.Table1 TO LoginB; 
GO 
EXECUTE AS USER = 'LoginB'; 
INSERT INTO dbo.Table1(id, tableContainer) 
VALUES (2, 'dbo'); 
REVERT; 
GO 

However, you denied the SELECT permission to LoginB. An explicit DENY for an ordinary user always supersedes all explicit GRANT. Therefore, the following code produces an error, stating that the SELECT permission is denied:

EXECUTE AS USER = 'LoginB'; 
SELECT * 
FROM dbo.Table1; 
REVERT; 
GO 

Finally, security would not worth much if a user could change their own settings. The following code impersonates LoginB and tries to change the permissions to the same database user:

EXECUTE AS USER = 'LoginB'; 
REVOKE SELECT ON dbo.Table1 FROM LoginB; 
REVERT; 
GO 

Of course, the previous code produced an error. However, you as the dbo database users, can change the permissions for the user LoginB, and therefore the following code succeeds:

REVOKE SELECT ON dbo.Table1 FROM LoginB; 
GO