© Peter A. Carter 2018
Peter A. CarterSecuring SQL Serverhttps://doi.org/10.1007/978-1-4842-4161-5_2

2. SQL Server Security Model

Peter A. Carter1 
(1)
London, UK
 

An early step in implementing countermeasures to potential threats in SQL Server is to ensure that you have a full understanding of the security model. SQL Server 2017 provides a rich security framework, with overlapping layers of security that help database administrators (DBAs) counter risks and threats in a manageable way.

It is important for DBAs to understand the SQL Server security model so that they can implement the technologies in the way that best fits the needs of their organization and applications, while minimizing the amount of security administration that is required. This chapter discusses the implementation of the security hierarchy in SQL Server 2016.

Active security refers to the practice of limiting users access to data and structures, with the use of permissions. When working with the SQL Server security model, the three entities to ensure that you understand are principals, securable, and permissions. The definition of each of these entities can be found in Table 2-1.
Table 2-1

Security Model Definitions

Entity

Definition

Principal

A security principal is an entity, such as a user.

Securable

A securable is data, an artifact, or metadata.

Permission

Permissions are rights that are granted or denied to a security principal, to define the principal’s permitted access to a securable.

Tip

Passive security refers to auditing activity. SQL Server Audit will be discussed in Chapter 3.

When implementing security in SQL Server, it is always important to consider and apply the principal of least privilege. In other words, users and other principals must only be given access to securables that they require to perform their day-to-day operations. For example, if the security principal UserA only needs to read data from the securable TableA, then UserA should only be given read permissions to TableA, not permission to write to the table.

Security Principal Hierarchy

Security principals are organized into a hierarchy, which allows administrators to assign permissions to a group of users. This has obvious benefits, allowing you to implement a security based on a user’s role within an organization. For example, all sales persons can easily be assigned the same permissions, if a pre-configured sales role exists, which already has all required permissions assigned to it. Figure 2-1 defines the complete hierarchy of security principals that can access data or data structures within SQL Server. The hierarchy begins at the domain level and passes through to the local server layer, the SQL Server instance layer, and finally the database layer.
../images/395795_2_En_2_Chapter/395795_2_En_2_Fig1_HTML.jpg
Figure 2-1

Security hierarchy

The diagram shows that a Login, created within the SQL Server instance, can be mapped to a local Windows user or group or to a domain user or group. Usually, in an Enterprise environment, this is a domain user or group. (A group is a collection of users that are granted permissions as a unit.) This eases the administration of security. Imagine (as described earlier in this section) that a new starter joins the sales team. When he is added to the domain group called SalesTeam, which already has all of the required permissions to file system locations, SQL Server databases, and so on, he will immediately inherit all required permissions to perform his role.

The diagram also illustrates how local server accounts or domain accounts and groups can be mapped to a user at the database level (a database user without login). This is part of the functionality of contained databases. This technology was introduced in SQL Server 2012 to support high availability with AlwaysOn Availability Groups. Contained database authentication is discussed later in this chapter.

SQL Server Logins, which are mapped to local server or domain level users or groups, are created at the SQL Server instance level. At the instance level, it is also possible to create SQL Server Logins, which use 2nd-tier authentication, if you are using mixed-mode authentication (explained in the Instance Level Security section of this chapter).

Both of these types of Login can be added to fixed server roles and user-defined server roles at the instance level. Doing this allows you to grant the principal a common set of permissions to instance-level objects, such as linked servers and endpoints. You can also map Logins to database users, which in turn can be granted permissions to database level objects.

Database users reside at the database level of the hierarchy. You can grant them permissions directly to schemas and objects within the database, or you can add them to database roles. Database roles are similar to server roles, except they are used to grant a common set of permissions at the database layer, instead of the instance layer. Database layer securables include schemas, tables, views, and stored procedures, etc.

Instance Level Security

Implementing instance level security involves creating and managing Logins, Credentials, and Server Roles. Securables at the instance level include databases, endpoints, and AlwaysOn Availability Groups. The following sections will discuss Logins, server roles, and credentials.

Tip

Cryptographic Providers and SQL Server Audits are also administered at the instance level. SQL Server Audit will be discussed in Chapter 3, and Cryptographic Providers will be discussed in Chapter 5.

Logins

Since SQL Server 2012, it has been possible for users to authenticate directly to a database, as part of contained database functionality. Generally, however, database engine users will need to authenticate at the instance level. SQL Server supports two authentication modes at the instance level: Windows Authentication and Mixed Mode Authentication.

When an instance is in Windows Authentication mode, users must authenticate to either the local server, or to the domain, before they can access the SQL Server instance. A Login is created, within the SQL Server instance, which maps to either their Windows user or a Windows group, which contains their Windows user. The SID (Security Identifier) of the Windows principal is stored in the Master database of the instance.

Tip

In addition to creating a login mapped to a Windows user or group, you can also map a login to a certificate or an asymmetric key. Doing so does not allow a user to authenticate to the instance by using a certificate, but it does allow for code signing so that permissions to procedures can be abstracted, rather than granted directly to a Login. This helps when you are using dynamic SQL, which breaks the ownership chain; in this scenario, when you run the procedure, SQL Server combines the permissions from the user who called the procedure and the user who maps to the certificate.

When an instance is configured to use Mixed Mode authentication, it is still possible to create a Login that maps to a Windows user or Windows group, but it is also possible to create 2nd-tier Logins, known as SQL Logins . These logins have their name, password, and SID stored in the Master database of the instance, and these details are verified when a user connects to the instance. A user can then authenticate to the instance using this user name and password, without the need for prior authentication to the server or domain.

Mixed Mode authentication is less secure than Windows authentication, because it is possible to attack the instance without first authenticating to the domain. Therefore, it is best practice us use Windows authentication. It is often necessary to use Mixed Mode authentication, however, for reasons such as:

  • Legacy applications that require a 2nd-tier Login

  • Access from outside of the domain (such as a Linux server)

  • Environments where security is implemented in the application tier and a single SQL Login connects to the database engine

Creating a Login

A login can be created in via T-SQL, using the CREATE LOGIN statement. When creating a Login from a Windows user or group, the syntax is very straightforward, as there is no password management involved. Table 2-2 details the WITH options that are valid, when creating a Login from a Windows security principal.
Table 2-2

CREATE LOGIN Options for Windows Security Principal

Option

Description

DEFAULT_DATABASE

Specifies a “landing” database for the Login. This is the database, to which their context will be scoped when they initially authenticate to the instance. This scope can be overwritten in the connection string.

DEFAULT_LANGUAGE

Specifies the default language that will be assigned to the Login. If omitted, then this is configured to be the default language of the instance.

Listing 2-1 demonstrates how to create a Login for the Windows user Pete in the CarterSecureSafe.com domain.
USE master
GO
CREATE LOGIN [cartersecuresafePete]
FROM WINDOWS
WITH DEFAULT_DATABASE=master, DEFAULT_LANGUAGE=British ;
GO
Listing 2-1

Create a Login from a Windows Security Principal

When creating a 2nd tier, SQL Login, however, there are many more WITH options that can be used. These options are described in Table 2-3.
Table 2-3

CREATE LOGIN Options for SQL Login

Option

Description

PASSWORD

Specifies the initial password that will be used by the Login, in clear text

PASSWORD HASHED

Specifies a hashed representation of the initial password that will be used by the Login*

SID

Specifies the SID of the Login*

DEFAULT_DATABASE

Specifies a “landing” database for the Login. This is the database, to which their context will be scoped when they initially authenticate to the instance. This scope can be overwritten in the connection string.

DEFAULT_LANGUAGE

Specifies the default language that will be assigned to the Login. If omitted, then this is configured to be the default language of the instance.

CHECK_POLICY

Specifies that the Login’s password must meet the same requirements, such as length and complexity, as Windows users, as enforced by Group Policy, or Local Security Policy.

CHECK_EXPIRATION

Specifies that the Login’s password will expire, in line with password expiration policy configured for Windows users, as enforced by Group Policy or Local Security Policy. This option is only valid if CHECK_POLICY is also specified.

MUST_CHANGE

Specifies that the user must change their password the first time that they log in to the instance. This option can only be used if CHECK_EXPIRATION is also specified.

*Will be discussed in the section Migrating Logins Between Instances

Listing 2-2 demonstrates how to create a SQL Login called Danni. The password for the Login must meet the complexity requirements specified by Group Policy, but the password will not expire. This means that it is not possible to force the password to be changed the first time that the user logs in.
USE master
GO
CREATE LOGIN Danni
WITH PASSWORD='C0mplexPa$$w0rd',
DEFAULT_DATABASE=master, CHECK_EXPIRATION=OFF, CHECK_POLICY=ON ;
GO
Listing 2-2

Create a SQL Login

Alternatively, logins can be created in SQL Server Management Studio. To demonstrate this, we will create a 2nd-tier Login Called Reuben. To do this, drill through [Instance name] | Security in Object Explorer and then select New Login from the context menu of Logins. This will cause the General page of the New Login dialog box to be displayed, as illustrated in Figure 2-2.
../images/395795_2_En_2_Chapter/395795_2_En_2_Fig2_HTML.jpg
Figure 2-2

New Login Dialog Box–General Page

We have provided the name for the Login in the Login name field at the top of the screen and chosen the radio button that indicates that it will be an SQL Server (2nd-tier Login). We can then enter the accounts password. Because the password is not shown as we type, we must enter it twice, to ensure that we have entered it correctly.

In the next section of the General page, we can specify which (if any) Windows policies should be applied to the Login. Remember that they are cumulative, so for example, you cannot select the option to Enforce password expiration, unless you also select the Enforce password policy option. At the bottom of the screen, we can specify a default database for the login to land in and the default language that should be configured for the Login.

Tip

You will notice that there are multiple pages of the dialog box, providing other management options. These will be discussed as you move through the chapter.

Migrating Logins Between Instances

There will be occasions where you need the same Login to exist on multiple servers. This may be because of a server migration, or it may be because you are implementing DR and need users to be able to reconnect to the DR instance transparently.

For Logins that are created from Windows security principals, this poses no problem whatsoever. As discussed earlier in this chapter, the SID of the Windows security principal is stored in the Master database, but the principal itself is managed by Windows. Therefore, you can simply create a Login on the second instance and map it to the same Windows user or group.

If you are working with SQL Logins, however, this scenario is more challenging to deal with, as the SQL Server instance hosts and manages the SID and the password of the Login. This means that if you create a Login with the same name and password on a different instance, then they will still be completely different, isolated principals. Once you move, or failover your databases to the second instance, the database users will become orphaned. This means that they no longer map to a login. This is because the login on the second instance has a different SID, despite having the same name and password.

You can manage this issue at point of failover by using an ALTER USER statement WITH LOGIN option.

Tip

The ALTER USER WITH LOGIN syntax replaces the functionality of the deprecated sp_change_users_logins system stored procedure.

Listing 2-3 demonstrates how to remap a database user called Danni, in the WideWorldImporters database , to a Login called Danni on a new instance.
USE WideWorldImporters
GO
ALTER USER Danni WITH LOGIN = Danni ;
Listing 2-3

Remap a Database User to a Login

Tip

Database users will be discussed in the Database Level Security section of this chapter.

Naturally, it is more efficient, however, if users are already mapped to their correct Login at the point that the databases are moved or failed over. This can be achieved by creating the Login and manually assigning it the correct SID.

This is where the WITH SID option mentioned in Table 2-1 comes into play. If you script out the SQL Login on an instance and include the SID, then you can use the script to pre-create the Login on the DR instance, with the correct SID, meaning that the database users will automatically map to the correct Login, without the need to alter them. Listing 2-4 demonstrates how this can be achieved using a SQLCMD script, which will replicate a Login called Danni from ProdInstance1 to DRInstance1.

Tip

The script below assumes instances named ProdInstance1 and DRInstance1 on a server named CARTERSECURESAFE. Please update the script to use your own server configuration. The script must be running in SQLCMD mode, or it will not execute.

:CONNECT CARTERSECURESAFEProdInstance1
DECLARE @SQL NVARCHAR(MAX) ;
SET @SQL = (SELECT 'CREATE LOGIN '
                        + name
                        + ' WITH PASSWORD = “C0mplexPassw0rd”, SID = 0x'
                        + CONVERT(NVARCHAR(64), SID, 2)
                FROM sys.sql_logins
                WHERE Name = 'Danni') ;
:CONNECT CARTERSECURESAFEDRInstance1
EXEC(@SQL) ;
Listing 2-4

Migrate a Login to a New Instance

Caution

This method works perfectly well in a SQLCMD script, although it is worth noting that depending on your server and network configuration, you may be sending the password across the wire in plain text. If this is the case, then you should follow the approach discussed next to hash the password.

An issue will arise with the approach that we have discussed, in a common scenario, where you wish to script out the Logins for an environment and keep them under source control, so that you can apply them to other environments as required. In this scenario you will be storing passwords in plain text. This poses an obvious security risk and should be avoided. Instead, you should script out the SQL Logins, so that the passwords are stored in the same encrypted format that SQL Server stores them in.

This approach can be achieved by scripting the Logins with passwords based on the password_hash column of the sys.sql_logins view. Instead of this, however, we will script the Logins using the HASHBYTES() function to generate the password hash for the Login. This technique is demonstrated in Listing 2-5 and gives you an insight into how SQL Server hashes the passwords. The script will generate the DDL (Data Definition Language) statements required to script out all enabled SQL Logins from an instance with hashed passwords. The script can subsequently be placed in source control and run on other environments as required.

Tip

In normal operations, you should take the approach of taking the password hash from the sys.sql_logins view. This is preferable, as it does not require knowledge of the Login’s plain text password.

The HASHBYTES() function returns a hash of its input. It accepts the parameters detailed in Table 2-4.
Table 2-4

HASHBYTES() Parameters

Parameter

Description

Algorithm

The algorithm that the function will use to hash the data. Acceptable values are:

• MD2

• MD4

• MD5

• SHA

• SHA1

• SHA2_256

• SHA2_512

Input

The value that will be hashed by the function

You will notice that the script uses a function called CRYPT_GEN_RANDOM(). This function uses the Windows CAPI (Crypto API) to generate a cryptographic random number, which it returns as a hexadecimal number. The function accepts the parameters detailed in Table 2-5.
Table 2-5

CRYP_GEN_RANDOM Parameters

Parameter

Description

Length

The length of the number to be generated

Seed

An optional randomization seed

DECLARE @password NVARCHAR(MAX) = 'C0mplexPa$$w0rd' ;
DECLARE @salt VARBINARY(4) = CRYPT_GEN_RANDOM(4) ;
DECLARE @hash VARBINARY(1000) ;
DECLARE @SQL NVARCHAR(MAX) ;
SET @hash = (SELECT 0x0200 + @salt + HASHBYTES('SHA2_512', CAST(@password AS VARBINARY(MAX)) + @salt)) ;
SET @SQL = (SELECT 'CREATE LOGIN '
                                + Name
                                + ' WITH PASSWORD = '
                                + CONVERT(NVARCHAR(1000), @hash, 1)
                                + ' HASHED, SID = 0x'
                                + CONVERT(NVARCHAR(64), SID, 2)
                        FROM sys.sql_logins
                        WHERE is_disabled = 0
                        FOR XML PATH(“)) ;
SELECT @SQL ;
Listing 2-5

Script Out Logins with Hashed Passwords

Server Roles

SQL Server provides a set of built-in server roles, out-of-the-box. These roles allow you to assign instance-level permissions to Logins that have common requirements. They are called fixed server roles , and it is not possible to change the permissions that are granted to them; you can only add and remove Logins. Table 2-6 describes each fixed server role.
Table 2-6

Fixed Server Roles

Role

Description

Sysadmin

The sysadmin role gives administrative permissions to the entire instance. A member of the sysadmin role can perform any action within the instance of the SQL Server relational engine.

Blkadmin

In conjunction with the INSERT permission on the target table within a database, the bulkadmin role allows a user to import data from a file using the BULK INSERT statement. This role is normally given to service accounts that run ETL processes.

Dbcreator

The dbcreator role allows its members to create new databases within the instance. Once a Login creates a database, that Login is automatically the owner of that database and is able to perform any action inside it.

Diskadmin

The diskadmin role gives its members the permissions to manage backup devices within SQL Server.

Processadmin

Members of the processadmin role are able to stop the instance from T-SQL or SSMS (SQL Server Management Studio). They are also able to kill running processes.

Public

All Logins are added to the public role. Although you can assign

permissions to the public role, this does not fit with the principle of least privilege. This role is normally only used for internal SQL Server operations, such as authentication to TempDB.

Securityadmin

Members of the securityadmin role are able to manage Logins at the instance level. For example, members may add a Login to a server role (except sysadmin) or assign permissions to an instance-level resource, such as an endpoint. However, they cannot assign permissions within a database to database users.

Serveradmin

serveradmin combines the diskadmin and processadmin roles. In addition to being able to start or stop the instance, however, members of this role can also shut down the instance using the SHUTDOWN T-SQL statement. The subtle difference here is that the SHUTDOWN command gives you the option of not running a CHECKPOINT in each database if you use it with the NOWAIT option. Additionally, members of this role can alter endpoints and view all instance metadata.

setupadmin

Members of the setupadmin role are able to create and manage linked servers.

You can also create custom server roles, which allow you to grant a custom set of permissions to a group of Logins. For example, if you implemented AlwaysOn Availability Groups, then you may wish to create a server role called AvailabilityRole and grant this group the following permissions:

  • Alter any availability group

  • Alter any endpoint

  • Create availability group

  • Create endpoint

You can then add the junior DBAs to this role, who are not authorized to be made members of the sysadmin fixed server role but who need to manage the high availability and disaster recovery of the instance. The script in Listing 2-6 demonstrates how to create the server role and grant it the relevant permissions.

You will notice that the script uses the GRANT statement to assign permissions to the role. When assigning permissions to a Server Role or Login, there are three assignments that can be made:

  • GRANT

  • DENY

  • REVOKE

GRANT provides a principal with the permissions to access a securable. You can use the WITH option in a GRANT statement to additionally provide a principal with the ability to GRANT the same permission to other principals. DENY specifically denies a principal’s permissions to access a securable; DENY always overrules GRANT. Therefore, if a Login is a member of a server role (or roles) that gives the Login permissions to alter an endpoint, but the principal itself has explicitly been denied permissions to alter the same endpoint, then the principal is not able to manage the endpoint. REVOKE removes a permission assignment to a securable. This includes DENY associations as well as GRANT associations. If a Login has been assigned permissions through a server role, however, then revoking the permissions to that securable, directly against the Login itself, has no effect. In order to have an effect, you would need to either use the DENY permission assignment against the Login or Server Role or REVOKE the permissions from the Server Role.
CREATE SERVER ROLE AVAILABILITYROLE AUTHORIZATION [CarterSecureSafeSQLAdmin] ;
GO
GRANT ALTER ANY AVAILABILITYROLE GROUP TO AVAILABILITYROLE ;
GRANT ALTER ANY ENDPOINT TO AVAILABILITYROLE ;
GRANT CREATE AVAILABILITYROLE GROUP TO AVAILABILITYROLE ;
GRANT CREATE ENDPOINT TO AVAILABILITYROLE ;
GO
Listing 2-6

Create Server Role and Grant Permissions

We could add the Login Danni to this Server Role by using the code in Listing 2-7.
ALTER SERVER ROLE AvailabilityRole ADD MEMBER Danni ;
GO
Listing 2-7

Add Login to Server Role

The Server Roles with which a Login is associated can also be managed through the Login Properties dialog box in SSMS. For example, imagine that we needed to make the Reuben Login an instance administrator. We could do this by drilling through Security | Logins in Object Explorer before double-clicking the Reuben Login to enter the Login Properties dialog box. Selecting the Server Roles page from the left will display a list of Server Roles, with check boxes, indicating which the Login is a member of. You can see in Figure 2-3 that we have checked the box next to sysadmin to make the login an administrator.
../images/395795_2_En_2_Chapter/395795_2_En_2_Fig3_HTML.jpg
Figure 2-3

Login properties-server roles

Credentials

Credentials are used to provide ability to access resources that are external to the SQL Server instance. SQL Logins can use credentials to access operating system level resources; SQL Server Agent Proxy Accounts use Credentials to access SQL Server Agent Subsystems, such as PowerShell or CmdExec; and Credentials are also used when taking backups to Azure.

When being used to access Operating System level resources, a credential will usually record the identity and password of a Windows security principal. If being used for backups to Azure, however, then the Credential will record the name and private key of the Azure storage account. Listing 2-8 demonstrates how to create a Credential to use for backups to Azure, where the storage account is called CarterSecureSafeStorageAcc .
CREATE CREDENTIAL URLBackupCredential
WITH IDENTITY = 'CarterSecureSafeStorageAcc'
                ,SECRET ='YdfgSGdTgJNpVFl992sBv7Bp1gyL61I33wNrTMHGBDdtVcx97F5f6SC5uDi59FeY2/IjxyqsuLU2xrkrNAGT==' ;
Listing 2-8

Create a Credential for Backups to Azure

Database-Level Security

At the database level, security is implemented by assigning permissions to security principals—namely, users and database roles. The following sections will discuss both of these types of principal.

Users

A database user will typically be created from a Login at the instance level. This means that the same instance level security principal can be granted permissions on resources in multiple databases. Since SQL Server 2012, however, it has also been possible to create a user without a Login. The following sections will describe each of these types of database user.

Users With a Login

Users can be created by using the CREATE USER T-SQL statement. Users that are created with an association to a Login have a limited set of options that can be configured. These options are detailed in Table 2-7.
Table 2-7

Options When Creating a User From a Login

Option

Description

DEFAULT_SCHEMA

Specifies the default schema for a user. Schemas will be discussed in Chapter 4.

ALLOW_ENCRYPTED_VALUE_MODIFICATIONS

Specifies that users will be allowed to bulk copy encrypted data, without first decrypting it. Encryption is discussed in Chapter 5.

We can create a user in the WideWorldImporters database, which is associated with the Login called Danni, by using the script in Listing 2-9.
USE WideWorldImporters
GO
CREATE USER Danni FOR LOGIN Danni
        WITH DEFAULT_SCHEMA = Sales ;
Listing 2-9

Create a User From a Login

The script creates a user that has the same name as the Login. Whilse this is not mandatory, it is sensible, as it aids the administration of the security principals and makes the hierarchy of principals transparent to new DBAs joining your team. The script sets the user’s default schema to be Sales. This means that the Danni user will be able to reference objects in the Sales schema using one-part names. If no default schema is specified for a user, then their default schema will be dbo.

We could also use the GUI to create this database user. One option here would be to drill through Databases | WideWorldImporters | Security in Object Explorer and then open the New User dialog box by selecting New from the context menu of users.

Another option would be to use the Login Properties dialog box, which you will already be familiar with, from the previous sections of this chapter. To create a user for the Reuben Login in the WideWorldImporters database, drill through Security | Logins in Object Explorer before double-clicking the Reuben Login and selecting the User Mapping page.

This page is illustrated in Figure 2-4. You will notice that the top pane contains a list of databases on the instance, with check boxes. Here, we have clicked the WideWorldImporters check box. Once a database is highlighted, the lower pane shows a list of database roles within that database. Again, these database roles have check boxes, so that the new user can be added to appropriate roles upon creation.
../images/395795_2_En_2_Chapter/395795_2_En_2_Fig4_HTML.jpg
Figure 2-4

Login properties–user mapping

Note

Schemas will be discussed in further detail in Chapter 4.

Users Without a Login

When creating a user that is not associated with a Login, the user can either be mapped to a Windows security principal, or it can be created using SQL Server authentication.

Note

Users can only be created using SQL Server authentication if the database is configured with a containment level of Partial. Contained databases are beyond the scope of this book, but you can learn more about them at https://msdn.microsoft.com/en-us/library/ff929071.aspx .

Table 2-8 details the properties that can be configured when creating a user without a Login.
Table 2-8

Options When Creating a User Without a Login

Option

Description

DEFAULT_SCHEMA

Specifies the default schema for a user. Schemas will be discussed in Chapter 4.

ALLOW_ENCRYPTED_VALUE_MODIFICATIONS

Specifies that users will be allowed to bulk copy encrypted data, without first decrypting it. Encryption is discussed in Chapter 5.

DEFAULT_LANGUAGE

Specify the default language for the user. This option can be expressed as an lcid, a language name, or a language alias.

SID

Specify the SID that will be associated with the user (SQL Server authentication only). This can be used to create users in multiple databases, that share the same SID. This can help with high availability and disaster recovery techniques, using AlwaysOn Availability Groups.

Listing 2-10 demonstrates how to create a user called Phil, from a Windows security principal called Phil, which exists in the Cartersecuresafe domain.
USE WideWorldImporters
GO
CREATE USER [cartersecuresafephil]
        WITH DEFAULT_SCHEMA=dbo ;
Listing 2-10

Create a User From a Windows Security Principal

As previously mentioned, creating users with SQL Authentication, also known as users with a password, is only possible in contained databases. If you attempt to create a user with SQL Authentication in a database that is not contained, you will receive the error displayed in Figure 2-5.
../images/395795_2_En_2_Chapter/395795_2_En_2_Fig5_HTML.jpg
Figure 2-5

Error when attempting to create a user with password in a non-contained database

The script in Listing 2-11 configures the instance to allow contained database authentication before configuring the WideWorldImporters database to support partial containment. Finally, the script creates a user with a password named Pete.
USE master
GO
EXEC sp_configure 'show advanced options', 1 ;
GO
RECONFIGURE ;
GO
EXEC sp_configure 'contained database authentication', '1' ;
GO
RECONFIGURE WITH OVERRIDE ;
ALTER DATABASE WideWorldImporters
        SET CONTAINMENT = PARTIAL
        WITH NO_WAIT ;
GO
USE WideWorldImporters
GO
CREATE USER Pete
        WITH PASSWORD = 'Pa$$w0rd123' ;
GO
Listing 2-11

Create a User With Password in a Contained Database

When you use contained database users, you need to take a number of additional security considerations into account. First, some applications may require a user have permissions to multiple databases. If the user is mapped to a Windows user or group, then this is straightforward because the SID that is being authenticated is that of the Windows object. If the database user is using 2nd-tier authentication, however, then you will need to duplicate the SID of the user from the first database. To do this, you will need to adhere to the following steps:
  1. 1.

    Create a user with password in the first database.

     
  2. 2.

    Retrieve the user’s SID from sys.database_principals.

     
  3. 3.

    Create the user in additional databases, specifically supplying the SID that you have recovered from the metadata.

     
The sys.database_principals catalog view exposes the columns detailed in Table 2-9.
Table 2-9

sys.database_principals Columns

Column

Description

name

The name of the security principal

principal_id

The ID of the security principal. This ID is only unique within the database.

type

A single character abbreviation of the type description

type_desc

A description of the type of security principal. Possible values are:

• APPLICATION_ROLE

• CERTIFICATE_MAPPED_USER

• EXTERNAL_USER

• WINDOWS_GROUP

• ASYMMETRIC_KEY_MAPPED_USER

• DATABASE_ROLE

• SQL_USER

• WINDOWS_USER

• EXTERNAL_GROUPS

default_schema_name

The name of the principals default schema

create_date

The date and time that the principal was created

modify_date

The date and time that the principal was last modified

owning_principal_id

The ID of the security principal that is marked as the owner of the principal. This will be 1, which is the ID of dbo, for all principals except database roles.

sid

The SID of the security principal

is_fixed_role

Indicates if the principal is a fixed database role.

• 1 indicates that the principal is a fixed database role.

• 0 indicates that the principal is not a fixed database role.

authentication_type

Indicates how the principal authenticates to the database. Possible values are listed below:

• 0 indicates No authentication.

• 1 indicates Instance authentication.

• 2 indicates Database authentication.

• 3 indicates Windows Authentication.

authentication_type_desc

A textual description of the authentication type. Possible values are:

• NONE

• INSTANCE

• DATABASE

• WINDOWS

default_language_name

The name of the default language assigned to the principal

default_language_lcid

The lcid of the default language assigned to the principal

Listing 2-12 demonstrates how to generate a list of user names and SID for users with a password in the WideWorldImporters database.
USE WideWorldImporters
GO
SELECT
        name
        ,sid
FROM sys.database_principals
WHERE authentication_type = 2 ;
Listing 2-12

Retrieve SIDs for Users With a Password

Database Roles

SQL Server provides a set of built-in database roles, out-of-the-box. These roles allow you to assign database-level permissions to users that have common requirements. These are called fixed database roles, and it is not possible to change the permissions that are granted to them; you can only add and remove users to and from the roles. Table 2-10 details each of these roles.
Table 2-10

Fixed Database Roles

Role

Description

db_accessadmin

Members of this role can add and remove database users from the database.

db_backupoperator

The db_backupoperator role gives users the permissions they need to back up the database, natively. It may not work for third-party backup tools, such as CommVault or Backup Exec, since these tools often demand sysadmin rights.

db_datareader

Members of the db_datareader role can run SELECT statements against any table in the database. It is possible to override this for specific tables by explicitly denying a user, the permissions to read those tables. DENY always overrides GRANT

db_datawriter

Members of the db_datawriter role can perform DML (Data Manipulation Language) statements against any table in the database. It is possible to override this for specific tables by specifically denying a user the permissions to write to a table. DENY will always override GRANT.

db_denydatareader

The db_denydatareader role denies the SELECT permission against every table in the database.

db_denydatawriter

The db_denydatawriter role denies its members the permissions to perform DLM statements against every table in the database.

db_ddladmin

Members of this role are given the ability to run CREATE, ALTER, and DROP statements against any object in the database. This role is rarely used, but I have seen a couple of examples of poorly written applications that create database objects on the fly. If you are responsible for administering an application such as this, then the ddl_admin role may be useful.

db_owner

Members of the db_owner role can perform any action within the database that has not been specifically denied.

db_securityadmin

Members of this role can GRANT, DENY, and REVOKE a user’s permissions to securables. They can also modify role memberships, with the exception of the db_owner role.

As well as the fixed roles, it is also possible to create your own user-defined database roles. This simplifies administration by allowing DBAs to create roles that map to requirements of business teams that use a specific database. For example, an administrator of the WideWorldImporters database may create a role for salespeople, a role for the procurement department and a role for the manufacturing department.

The script in Listing 2-13 demonstrates how to create the role for the sales team. The role will be granted SELECT, INSERT, and UPDATE permissions on the sales schema of the WideWorldImporters database and the user, Danni, will be made a member of the role. The role will be called SalesRole and will be owned by dbo.
USE WideWorldImporters
GO
--Create the role
CREATE ROLE SalesRole AUTHORIZATION dbo ;
GO
--Grant permissions to the role
GRANT DELETE ON SCHEMA::Sales TO SalesRole ;
GRANT INSERT ON SCHEMA::Sales TO SalesRole ;
GRANT SELECT ON SCHEMA::Sales TO SalesRole ;
GRANT UPDATE ON SCHEMA::Sales TO SalesRole ;
--Add user to the role
ALTER ROLE SalesRole ADD MEMBER Danni ;
Listing 2-13

Create SalesRole

Summary

SQL Server provides a flexible hierarchy to implement security. Role-based security is available out-of-the-box, and database administrators should embrace this in order to simplify the administration of security.

The database engine supports two methods of authentication: Windows authentication and SQL Server authentication. The latter is SQL Server’s implementation of 2nd-tier authentication. Windows authentication should be used, unless there is a good reason not to, as it is more secure.

Database engine users will typically authenticate to the instance via a Login. This Login will then map to users within the databases to which they require access. It is also possible, however, for users to authenticate directly to a database. This is known as a user without a Login. When a user without a Login is implemented, they can authenticate via Windows authentication, or if the database is contained, then SQL Server authentication can alternatively be used.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset