Chapter 2 User authentication, Authorization, and Security (10): Create include database __ Database

原文出处：http://blog.csdn.net/dba_huangzj/article/details/39473895，专题目录：http://blog.csdn.net/dba_huangzj/article/details/37906349

No person shall, without the consent of the author, publish in the form of "original" or be used for commercial purposes without any liability.

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

Preface:

In SQL Server, security is divided into two levels: server-level and database-level. The server's login name is mapped to a user in the database. Authentication occurs when the logon name is connected to the server. If the user in the database has this login, the login can access the database. The mapping between the login name and the user is based on an internal SID, and when the database is replicated from one server to another, even if the user name is the same, the connection is intended if the SID is different. The inclusion database (contained) concept has been introduced from 2012 to address this problem.

A containing database does not depend on any external definition, and can be moved freely between servers without any additional configuration required. The inclusion concept of SQL Server has the following 3 classes: not included (non-contained): Database server-based. Partial inclusion (partially-contained): The user is defined within the database, the database is independent, but still has access to resources outside the database. Fully contained (fully-contained): The database is independent and the user cannot access external resources.

SQL Server 2012 supports only partial inclusion, and this feature addresses two issues: logins and user mappings, and temporary table sorting rules. 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 called database, not tempdb.

preparatory work:

You need to enable the inclusion of database authentication at the instance level before you create the include database. Right key 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 should also be enabled on the server that needs to be restored (assuming that your library needs to be moved to another server).

implementation:

You can create a partial inclusion database by following these steps:

1. In SSMs, right-click the database node 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. Then, you can create a database inside the user:

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 displayed 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 able to connect to SQL Server, and now users can be directly authorized to do so independently of any login. The 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 a login mapping at the server level.

A containing user does not have a default database, so if the database is not explicitly defined at the time the connection will not be created, it can be defined through the SSMs connection properties or in the connection string in the application, such as using 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 include database, you can use DDL triggers to modify the containing type of an existing database because the target server may already have the same login name, which may cause some security vulnerabilities or permissions conflicts. Detailed content can look at 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 in other databases, or synonyms, server-level system objects, it is difficult to implement a true inclusion 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/synony 
                                                              M Target/route/user/login] = COALESCE (S.[base_object_name], 
                                                              SUBSTRING (M.[definition], E.statement_offset_begin 
                                                              /2, 
                                                              E.statement_offset_end/2 
                                                              -E.statement_offset_begin /2) COLLATE Catalog_DEFAULT, R.[name], 
                                                              ' User: ' + p.[name] 
                                                              + '/Login: ' + Sp.[name]) from Sys.dm_db_uncontained_entities as E-left JOIN sys.objects as o on e.major_id = O.objec t_id and E.class = 1 left JOIN sys.sql_modules as m on e.major_id = M.obje ct_id and E.class = 1 left JOIN sys.synonyms as s on e.major_id = S.ob ject_id and E.class = 1 left JOIN sys.routes as r on e.major_id = R.route  _id and E.class = left JOIN 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 to include a 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 a user maps 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 ', 
  @ Disablelogin = N ' Do_not_disable_login '; 
From Sys.database_principals as DPS 
JOIN sys.server_principals as sp on 
dp.sid = Sp.sid 
WHERE Dp.authentication_type = 1 and sp.is_disabled = 0;

Next: http://blog.csdn.net/dba_huangzj/article/details/39496517