Chapter 2 User authentication, Authorization, and Security (10): Create a containing database

Original source: http://blog.csdn.net/dba_huangzj/article/details/39473895, featured catalogue:http://blog.csdn.net/dba_huangzj/article/details/37906349

No person shall, without the consent of the author, be published in the form of "original" or used for commercial purposes, and I am not responsible for any legal liability.

Previous article: http://blog.csdn.net/dba_huangzj/article/details/39003679

Objective:

In SQL Server, security is classified as Level two: server-level and database-level. The login name of the server is mapped to a user in the database. Authentication occurs when the logon name is connected to the server. If the user is in the database with the login name, the login will be able to access the database. The mapping between logins and users is based on an internal SID that, when replicating a database from one server to another, even if the user name is the same, the connection is intended if the SID is different. The Contained database concept has been introduced from 2012 to address this problem.

A contained database is not dependent on any external definitions and can be moved freely between servers without any additional configuration required. The inclusion concept of SQL Server has the following 3 categories:

• Not included (non-contained): Database is server-based.
• Partially contained (partially-contained): The user is defined within the database, the database is independent, but the resources outside the database can still be accessed.
• Fully contained (fully-contained): The database is independent and the user cannot access external resources.

SQL Server 2012 only supports partial inclusion, which mainly addresses two issues: the mapping of logins and users, and the collation of temporary tables. When a local temporary table is created within the scope of the containing database, the collation of the Char/varchar column is determined by the collation of the calling database, not tempdb.

Preparatory work:

Before you can create a containing database, you need to enable include database authentication at the instance level. Right-click instance → "Properties" → "Advanced"

You can also use T-SQL implementations:

EXEC sp_configure ' show advanced options ', 1; RECONFIGURE; GO EXEC sp_configure ' contained database authentication ', 1; RECONFIGURE; GO sp_configure ' show advanced options ', 0; RECONFIGURE; GO

This configuration is also enabled on the server that needs to be restored (assuming that your library needs to be moved to another server).

Realize:

You can create a partially contained database by following these steps:

1. Right-click the Databases node in SSMS and select New Database

2. In the Options page, select section in the Include Type option

Change to T-SQL is:

CREATE DATABASE containeddb containment = PARTIAL;

3. You can then create a user inside a database:

Use CONTAINEDDB; CREATE USER Fred with PASSWORD = N ' strong PASSWORD ', –sql authentication and password default_schema = dbo; CREATE USER [domain\fred];-windows Authentication

4. To view the included users in the containing database, you can use the following statement:

SELECT name, Type_desc, Authentication_type_desc from sys.database_principals WHERE authentication_type = 2;

5. The authentication used is shown in the DMV sys.dm_exec_sessions:

SELECT  session_id,         login_time,         login_name,         db_name (database_id) as DB,     IIF (         authenticating_database_id = 1,         ' server login ',         QUOTENAME (db_name (authenticating_database_id)) + ' user '         + QUOTENAME (original_login_name))     As Authentication_type from    sys.dm_exec_sessions WHERE   is_user_process = 1;

Principle:

The inclusion database has brought some changes to the traditional SQL Server security model. In the past, only authorized logins were available to connect to SQL Server, and now users can be directly authorized to do so independently of any login. Its password is stored at the database level and can be modified at the server level. If the containing database user is a Windows account, the entire account is valid only within the database because it does not have login mappings at the server level.

A containing user does not have a default database, so if the database is not explicitly defined at the time of connection, the connection cannot be created and can be defined through the connection properties of SSMs or in the connection string in the application, such as using the SQL Server Native Client ODBC driver:

Driver={sql Server Native Client 11.0}; SERVER=SERVER\SQL2012;DATABASE=CONTAINEDDB; uid=fred; Pwd=iamaweakpassword;

When you create a containing database, you can use DDL triggers to respond to the inclusion type modification of an existing database because the target server may already have the same login, which can cause some security breaches or permission conflicts. For more information, see the following blog:

http://blogs.msdn.com/b/sqlsecurity/archive/2010/12/06/ Contained-database-authentication-how-to-control-which-databases-are-allowed-to-authenticate-users-using-logon-triggers.a Spx

More:

Because some views, stored procedures may depend on tables from other databases, or synonyms, server-level system objects, it is difficult to implement a real containment database. So in SQL Server 2012, the containing database is partially contained. You can query this DMV from Sys.dm_db_uncontained_entities:

 SELECT e.feature_name, [Object] = COALESCE (QUOTENAME (Schema_name (o.[schema_id])) + '. '                             + QUOTENAME (O.[name]), QUOTENAME (Schema_name (s.[schema_id])) + '. ' + QUOTENAME (S.[name])), [line] = COALESCE (e.statement_line_number, 0), [statement/synonym Targe T/route/user/login] = COALESCE (S.[base_object_name], SUB                                                               STRING (M.[definition], E.statement_offset_begin /2, E.sta                                                               Tement_offset_end/2 -E.STATEMENT_OFFSET_BEGIN/2) COLL      ATE Catalog_default,                                                         R.[name],                                                               ' User: ' + p.[name] + '/Login: ' + sp.[name]) F                                       ROM Sys.dm_db_uncontained_entities as E left joins sys.objects as O on e.major_id = o.object_id                                           and E.class = 1 left JOIN sys.sql_modules as m on e.major_id = m.object_id                                        and E.class = 1 left JOIN sys.synonyms as s on e.major_id = s.object_id                                      and E.class = 1 left JOIN sys.routes as r on e.major_id = r.route_id                                      and E.class = sys.database_principals as P on e.major_id = p.principal_id             and E.class = 4 left JOIN sys.server_principals as SP on p.[sid] = Sp.[sid]; 

How to convert a database into a containing database:

You can use the following statement to convert a database into a containing database:

Use [master] GO ALTER DATABASE [marketing] SET containment = PARTIAL;

If you have a user mapping to a SQL login, you can use the sp_migrate_user_to_contained system stored procedure to convert the containing database user, and if you need automation, you can view the Microsoft documentation: http://msdn.microsoft.com/en-us /library/ff929275.aspx, you can also use the following script:

SELECT ' EXEC sp_migrate_user_to_contained @username = N ' + dp.name + ',   @rename = n ' keep_name ',   @disablelog in = N ' do_not_disable_login '; ' From Sys.database_principals as DP JOIN sys.server_principals as SP in dp.sid = sp.sid WHERE Dp.authentication_type = 1 An D sp.is_disabled = 0;

Next article:

Chapter 2 User authentication, Authorization, and Security (10): Create a containing database