{"id":83448,"date":"2019-02-25T15:59:49","date_gmt":"2019-02-25T15:59:49","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=83448"},"modified":"2022-04-24T21:15:30","modified_gmt":"2022-04-24T21:15:30","slug":"introduction-to-sql-server-security-part-3","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/devops\/data-privacy-and-protection\/introduction-to-sql-server-security-part-3\/","title":{"rendered":"Introduction to SQL Server Security \u2014 Part 3"},"content":{"rendered":"

The series so far:<\/h4>\n
    \n
  1. Introduction to SQL Server Security \u2014 Part 1<\/a><\/li>\n
  2. Introduction to SQL Server Security \u2014 Part 2<\/a><\/li>\n
  3. Introduction to SQL Server Security \u2014 Part 3<\/a><\/li>\n
  4. Introduction to SQL Server Security \u2014 Part 4<\/a><\/li>\n
  5. Introduction to SQL Server Security \u2014 Part 5<\/a><\/li>\n
  6. Introduction to SQL Server Security\u00a0\u2014 <\/span>Part 6<\/a><\/li>\n
  7. \u00a0<\/li>\n<\/ol>\n\n

    Microsoft introduced contained databases in SQL Server 2012. A contained database is one that is isolated from other databases and from the SQL Server instance that hosts the database. The database maintains much of its own metadata and supports database-level authentication, eliminating the need for server-based logins. As a result, a contained database is more portable than a traditional, non-contained database. It can also simplify database development and administration, as well as make it easier to support Always On Availability Groups.<\/p>\n

    Controlling access to a contained database is similar to a non-contained database, except for a few important differences. In the first two articles in this series, I touched briefly upon the topic of contained databases when discussing SQL Server access control. In this article, I dig deeper into contained databases and offer several examples that show how to create contained database users, duplicate users across multiple contained databases, and unlink database users from their server-level logins.<\/p>\n

    Setting Up Your Environments<\/h2>\n

    To try out the examples in this article, you need a test environment that includes a contained database. On my system, I used SQL Server Management Studio (SSMS) to create a simple database and populate it with data from the WideWorldImporters<\/code> database, although you can use any data that fits your needs.<\/p>\n

    Before you can implement a contained database, you must enable the SQL Server instance to support this feature, if it\u2019s not already enabled. To use T-SQL to enable contained databases, run the following EXECUTE<\/code> statement:<\/p>\n

    EXEC sp_configure 'contained database authentication', 1;\r\nGO\r\nRECONFIGURE;\r\nGO<\/pre>\n
    The EXECUTE<\/code> statement calls the sp_configure<\/code> stored procedure to set the contained<\/code> database<\/code> authentication<\/code> setting to 1<\/code> (on). You should then run the RECONFIGURE<\/code> statement to implement the changes.<\/p>\n
    For the examples in this article, create the ImportSales1<\/code> contained database, using the following T-SQL script:<\/p>\n
    USE master;\r\nGO\r\nDROP DATABASE IF EXISTS ImportSales1;\r\nGO\r\nCREATE DATABASE ImportSales1\r\nCONTAINMENT = PARTIAL;\r\nGO<\/pre>\n
    When you create a database, you can specify that it should be contained by including the CONTAINMENT<\/code> clause in the CREATE<\/code> DATABASE<\/code> statement and set its value to PARTIAL<\/code>. The default value is NONE<\/code>, which disables the contained database feature. The PARTIAL<\/code> value is used because SQL Server supports only partially contained databases, as opposed to fully contained databases. Currently, SQL Server does not support fully contained databases.<\/p>\n
    A partially contained database allows you to implement uncontained features that cross the database boundary. For example, you can create a database user that is linked to a SQL Server login in a partially contained database. Fully contained databases do not allow the use of uncontained features.<\/p>\n
    After you create the ImportSales1<\/code> database, you can add tables and then populate them, just like you can with a non-contained database. To support the examples in the rest of the article, use the following T-SQL script:<\/p>\n
    USE ImportSales1;\r\nGO\r\nCREATE SCHEMA Sales;\r\nGO\r\nCREATE TABLE Sales.Customers(\r\n  CustID INT NOT NULL PRIMARY KEY,\r\n  Customer NVARCHAR(100) NOT NULL,\r\n  Contact NVARCHAR(50) NOT NULL,\r\n  Category NVARCHAR(50) NOT NULL);\r\nGO\r\nINSERT INTO Sales.Customers(CustID, Customer, Contact, Category) \r\nSELECT CustomerID, CustomerName, \r\n  PrimaryContact, CustomerCategoryName\r\nFROM WideWorldImporters.Website.Customers\r\nWHERE BuyingGroupName IS NOT NULL;\r\nGO<\/pre>\n
    The script creates the Sales<\/code> schema, adds the Customers<\/code> table to the schema, and then populates the table with data from the WideWorldImporters<\/code> database. The SELECT<\/code> statement\u2019s WHERE<\/code> clause limits the results to those rows with a BuyingGroupName<\/code> value that is NOT<\/code> NULL<\/code> (402 rows on my system). If you create a different structure or use different data, be sure to modify the remaining examples as necessary.<\/p>\n
    Creating Database Users<\/h2>\n
    In SQL Server, you can create users that are specific to a contained database and not linked to server-level logins. Contained users make it possible to maintain the separation between the contained database and the SQL Server instance, so it\u2019s easier to move the database between instances.<\/p>\n
    SQL Server supports two types of contained users: SQL user with password<\/em> and Windows user<\/em>. The password-based user is a database user that is assigned a password for authenticating directly to the database. The user is not associated with any Windows accounts.<\/p>\n
    To create a password-based user, you must include a WITH<\/code> PASSWORD<\/code> clause in your CREATE<\/code> USER<\/code> statement. For example, the following CREATE<\/code> USER<\/code> statement defines a user named sqluser02<\/code> and assigns the password tempPW@56789<\/code> to the user:<\/p>\n
    USE ImportSales1;\r\nGO\r\nCREATE USER sqluser02\r\nWITH PASSWORD = 'tempPW@56789';\r\nGO<\/pre>\n
    When a password-based user tries to access a contained database, the user account is authenticated at the database level, rather than the server level. In addition, all authorization granted through assigned permissions is limited to the database.<\/p>\n
    The second type of contained database user\u2014Windows user<\/em>\u2014is based on a Windows account, either local or domain. The Windows computer or Active Directory service authenticates the user and passes an access token onto the database. As with password-based users, authorization also occurs within the database according to how permissions have been granted or denied.<\/p>\n
    When you create a Windows user, be sure that the Windows account is not already associated with a login. If you try to create a Windows user with the same name as a login, SQL Server will automatically associate that user with the login, which means that the user will not be contained.<\/p>\n
    In the following example, the CREATE<\/code> USER<\/code> statement defines a user based on the winuser02<\/code> local account, which I created on the win10b<\/code> computer:<\/p>\n
    CREATE USER [win10b\\winuser02];\r\nGO<\/pre>\n
    Whenever referencing a Windows account in this way, you must use the following format, including the brackets (unless enclosing the account in quotes):<\/p>\n
    [<domain_or_computer>\\<windows_account>]<\/pre>\n
    After you\u2019ve created your contained users, you can grant, deny, or revoke permissions just like you can with any database users. For example, the following GRANT<\/code> statement grants the SELECT<\/code> permission on the Sales<\/code> schema to both users:<\/p>\n
    GRANT SELECT ON SCHEMA::Sales TO sqluser02, [win10b\\winuser02];\r\nGO<\/pre>\n
    You can also add contained users to fixed and user-defined database roles, and assign permissions to the user-defined roles. For more information about creating database users and granting them permissions, refer back to the second article in this series.<\/p>\n
    Creating Duplicate Database Users<\/h2>\n
    When working with contained databases, you might find that some users need to be able to access multiple databases. For password-based users (SQL user with password<\/em>), you should create the same user in each database, assigning the same password and security identifier (SID) to each user instance.<\/p>\n
    One way to get the SID is to retrieve it from the sys.database_principals<\/code> system view after creating the first user, as shown in the following example:<\/p>\n
    USE ImportSales1;\r\nGO\r\nSELECT SID FROM sys.database_principals WHERE name = 'sqluser02';<\/pre>\n
    The SELECT<\/code> statement returns the SID<\/code> value for the sqluser02<\/code> user in the ImportSales1<\/code> database. The returned value will be unique to that user and will be in a form similar to the following:<\/p>\n
    0x0105000000000009030000008F5AC110DFB07044AFDADA6962B63B03<\/pre>\n
    You should use this value whenever you duplicate the user in other contained databases. To see how this works, you can create a database similar to the ImportSales1<\/code> database but instead name it ImportSales2<\/code>, as in the following example:<\/p>\n
    USE master;\r\nGO\r\nDROP DATABASE IF EXISTS ImportSales2;\r\nGO\r\nCREATE DATABASE ImportSales2\r\nCONTAINMENT = PARTIAL;\r\nGO\r\nUSE ImportSales2;\r\nGO\r\nCREATE SCHEMA Sales;\r\nGO\r\nCREATE TABLE Sales.Customers(\r\n  CustID INT NOT NULL PRIMARY KEY,\r\n  Customer NVARCHAR(100) NOT NULL,\r\n  Contact NVARCHAR(50) NOT NULL,\r\n  Category NVARCHAR(50) NOT NULL);\r\nGO\r\nINSERT INTO Sales.Customers(CustID, Customer, Contact, Category) \r\nSELECT CustomerID, CustomerName, \r\n  PrimaryContact, CustomerCategoryName\r\nFROM WideWorldImporters.Website.Customers\r\nWHERE BuyingGroupName IS NULL;\r\nGO<\/pre>\n
    The script creates the ImportSales2<\/code> database, adds the Sales<\/code> schema to the database, adds the Customers<\/code> table to the schema, and populates the table with 261 rows of data from the WideWorldImporters<\/code> database. In this case, the WHERE<\/code> clause filters for NULL<\/code> values, rather than NOT<\/code> NULL<\/code>.<\/p>\n
    Next, create the sqluser02<\/code> user in the ImportSales2<\/code> database, only this time, include an SID<\/code> clause that specifies the user\u2019s SID from the ImportSales1<\/code> database, as shown in the following example:<\/p>\n
    USE ImportSales2;\r\nGO\r\nCREATE USER sqluser02\r\nWITH PASSWORD = 'tempPW@56789',\r\nSID = 0x0105000000000009030000008F5AC110DFB07044AFDADA6962B63B03;\r\nGO<\/pre>\n
    To create a duplicate Windows-based user, use the same CREATE<\/code> USER<\/code> statement you used in the ImportSales1<\/code> database:<\/p>\n
    CREATE USER [win10b\\winuser02];\r\nGO<\/pre>\n
    You can also use the same GRANT<\/code> statement to assign the SELECT<\/code> permission to the Sales<\/code> schema for both users:<\/p>\n
    GRANT SELECT ON SCHEMA::Sales TO sqluser02, [win10b\\winuser02];\r\nGO<\/pre>\n
    That\u2019s all there is to creating duplicate password-based and Windows-based users. You can use the same format for creating duplicate users in additional contained databases, depending on your data access requirements.<\/p>\n
    Running T-SQL Queries<\/h2>\n
    To test the users you created in the contained databases, you can use an EXECUTE<\/code> AS<\/code> statement in SSMS to run queries within the execution context of a specific contained user. For example, the following T-SQL sets the execution context to the sqluser02<\/code> user, runs a SELECT<\/code> statement against the Customers<\/code> table, and then uses the REVERT<\/code> statement to return to the original execution context:<\/p>\n
    EXECUTE AS USER = 'sqluser02'; \r\nSELECT * FROM Sales.Customers\r\nREVERT;\r\nGO <\/pre>\n
    On my system, the SELECT<\/code> statement returns 261 rows because the statement ran within the context of the last specified database, ImportSales2<\/code>. However, the sqluser02<\/code> user exists in both databases, sharing the same name, password, and SID, so you should be able to query the Customers<\/code> table in both databases, as in the following example:<\/p>\n
    EXECUTE AS USER = 'sqluser02'; \r\nSELECT * FROM ImportSales1.Sales.Customers\r\nUNION ALL\r\nSELECT * FROM ImportSales2.Sales.Customers;\r\nREVERT;  \r\nGO <\/pre>\n
    Unfortunately, if you try to run the statement, you\u2019ll receive an error similar to the following:<\/p>\n
    The server principal \"S-1-9-3-281107087-1148235999-1775950511-54244962\" \r\nis not able to access the database \"ImportSales1\" under \r\nthe current security context.<\/pre>\n
    The problem is not with how you\u2019ve set up the user accounts or query, but rather with how the TRUSTWORTHY<\/code> database property is configured. The property determines whether the SQL Server instance trusts the database and the contents within it. Although this might seem to have little to do with contained databases, the TRUSTWORTHY<\/code> property must be set to ON<\/code> for the ImportSales2<\/code> database because you\u2019re running the query within the context of that database but trying to access data in the ImportSales1<\/code> database.<\/p>\n
    By default, the TRUSTWORTHY<\/code> property is set to OFF<\/code> to reduce certain types of threats. You can find more information about the property in the SQL Server help topic TRUSTWORTHY Database Property<\/a>.<\/p>\n
    Before setting the property, you must be sure you\u2019re working in the correct execution context. If you\u2019ve been following along with the examples, your session might still be operating within the context of the sqluser02<\/code> user. This is because the UNION<\/code> ALL<\/code> query in the last example failed, which means that the REVERT<\/code> statement never ran. As a result, your current SQL Server session is still be running within the execution context of the sqluser02<\/code> user. To correct this situation, simply rerun the REVERT<\/code> statement.<\/p>\n
    At any point, you can verify the current execution context by calling the CURRENT_USER<\/code> function:<\/p>\n
    SELECT CURRENT_USER;<\/pre>\n
    Once you\u2019ve established that you\u2019re working within the context of the correct user, run the following ALTER<\/code> DATABASE<\/code> statement to set the TRUSTWORTHY<\/code> property to ON<\/code>:<\/p>\n
    ALTER DATABASE ImportSales2 SET TRUSTWORTHY ON;\r\nGO<\/pre>\n
    Now when you run the following query, it should return the 663 rows from the two tables:<\/p>\n
    EXECUTE AS USER = 'sqluser02'; \r\nSELECT * FROM ImportSales1.Sales.Customers\r\nUNION ALL\r\nSELECT * FROM ImportSales2.Sales.Customers;\r\nREVERT;\r\nGO <\/pre>\n
    You should also receive the same results if you run the query under the execution context of the win10b\\winuser02<\/code> user, as shown in the following example:<\/p>\n
    EXECUTE AS USER = 'win10b\\winuser02'; \r\nSELECT * FROM ImportSales1.Sales.Customers\r\nUNION ALL\r\nSELECT * FROM ImportSales2.Sales.Customers;\r\nREVERT;\r\nGO<\/pre>\n
    I created and ran all the above examples in SSMS. If you try them out for yourselves, you\u2019ll also likely use SSMS or SQL Server Data Tools (SSDT). In the real world, however, most connections will be through third-party scripts, utilities, or applications. In such cases, the connection string that establishes the connection to the contained database must specify that database as the initial catalog. Otherwise the connection will fail.<\/p>\n
    Unlinking Database Users from Their Server Logins<\/h2>\n
    Because SQL Server contained databases are only partially contained, they can include users mapped to server logins. The users might have existed before changing the database to a contained state, or they might have been added after the fact. In either case, the database is less portable because of its login connections.<\/p>\n
    SQL Server provides the sp_migrate_user_to_contained<\/code> system stored procedure for quickly unlinking database users from their associated SQL Server logins. To see how this works, start by creating the following user in the ImportSales1<\/code> database:<\/p>\n
    USE ImportSales1;\r\nGO\r\nCREATE USER sqluser03 FOR LOGIN sqluser01;\r\nGRANT SELECT ON SCHEMA::Sales TO sqluser03;\r\nGO<\/pre>\n
    The script creates the sqluser03<\/code> user based on the sqluser01<\/code> login and grants to the user the SELECT<\/code> permission on the Sales<\/code> schema. (If the sqluser01<\/code> login doesn\u2019t exist on your system, you can also use a different login or refer to the second article in this series for information about creating the sqluser01<\/code> login.)<\/p>\n
    After you create the database user, you can test that it has the expected access by running the following query:<\/p>\n
    EXECUTE AS USER = 'sqluser03'; \r\nSELECT * FROM ImportSales1.Sales.Customers;\r\nREVERT;\r\nGO <\/pre>\n
    The query should return all the rows from the Customers<\/code> table in the ImportSales1<\/code> database.<\/p>\n
    If you view the user\u2019s properties in Object Explorer in SSMS, you\u2019ll find that the General<\/code> tab shows the associated login as sqluser01<\/code> and the user type as SQL<\/code> user<\/code> with<\/code> login<\/code>, as shown in Figure 1<\/p>\n
    <\/p>\n
    Figure 1. Database user based on a SQL Server login<\/p>\n
    To unlink this user from the SQL Server login, run the sp_migrate_user_to_contained<\/code> stored procedure, specifying the database user that you want to migrate, as shown in the following example:<\/p>\n
    EXEC sp_migrate_user_to_contained   \r\n@username = N'sqluser03',  \r\n@rename = N'keep_name',  \r\n@disablelogin = N'do_not_disable_login';<\/pre>\n
    The sp_migrate_user_to_contained<\/code> system stored procedure takes the following three parameters:<\/p>\n