Creating a Login

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

Listing 2-1 demonstrates how to create a login for the Windows user Pete in the CarterSecureSafe.com domain.

Listing 2-1. Create a Login from a Windows Security Principal

USE master
GO

CREATE LOGIN [cartersecuresafePete]
FROM WINDOWS
WITH DEFAULT_DATABASE=master, DEFAULT_LANGUAGE=British ;
GO

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

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.

Listing 2-2. Create a SQL Login

USE master
GO

CREATE LOGIN Danni
WITH PASSWORD='C0mplexPa$$w0rd',
DEFAULT_DATABASE=master, CHECK_EXPIRATION=OFF, CHECK_POLICY=ON ;
GO

Migrating Logins Between Instances

There will be occasions when 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 you 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 what so ever. 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, because 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 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 LOGINoption.

Listing 2-3 demonstrates how to remap a database user called Danni in the AdventureWork2016 database to a login called Danni on a new instance.

Listing 2-3. Remap a Database User to a Login

USE AdventureWorks2016
GO

ALTER USER Danni WITH LOGIN = Danni ;

Naturally, it is more efficient 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 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 replicates a login called Danni from ProdInstance1 to DRInstance1.

Listing 2-4. Migrate a Login to a New Instance

: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) ;

An issue arises with the approach that we have discussed; it’s a common scenario, in which you want 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 store passwords in plain text. This poses an obvious security risk and it 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 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 generates 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.

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

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.

Listing 2-5. Script out Logins with Hashed Passwords

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 ;

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 described in Table 2-7.

You can create a user in the AdventureWorks2016 database, which is associated with the login called Danni, by using the script in Listing 2-9.

Listing 2-9. Create a User from a Login

USE AdventureWorks2016
GO

CREATE USER Danni FOR LOGIN Danni
        WITH DEFAULT_SCHEMA = Sales                                                           ;

The script creates a user that has the same name as the login. Whilst this is not mandatory, it is sensible, because it aids the administration of the security principals and makes the hierarchy of principals transparent to new DBA s joining your team. The script sets the user’s default schema to Sales. This means that the Danni user is 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 is dbo.

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.

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

Listing 2-10 demonstrates how to create a user called Phil from a Windows security principal called Phil, which exists in the cartersecuresafe domain.

Listing 2-10. Create a User from a Windows Security Principal

USE AdventureWorks2016
GO

CREATE USER [cartersecuresafephil]
        WITH DEFAULT_SCHEMA=dbo ;

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 receive the error shown in Figure 2-2.

Figure 2-2. 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 AdventureWorks2016 database to support partial containment. Finally, the script creates a user, named Pete, with a password.

Listing 2-11. Create a User with Password in a Contained Database

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 AdventureWorks2016
        SET CONTAINMENT = PARTIAL
        WITH NO_WAIT ;
GO

USE AdventureWorks2016
GO

CREATE USER Pete
        WITH PASSWORD = 'Pa$$w0rd123' ;
GO

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 second-tier authentication, however, then you need to duplicate the user’s SID from the first database. To do this, you need to adhere to the following steps:

1. Create a user with password in the first database .

2. Retrieve the user’s SID from sys.database_principals.

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.

Listing 2-12 demonstrates how to generate a list of usernames and SID for users with a password in the AdventureWorks2016 database.

Listing 2-12. Retrieve SIDs for Users with a Password

USE AdventureWorks2016
GO

SELECT
        name
        ,sid
FROM sys.database_principals
WHERE authentication_type = 2 ;