SQL Server Certified Inclusion database

Contained database: The role is that the user who created the database does not need to specify a login name to access the connection.

Advantage: The database does not need to create a login name when it is recovery. When migrating, you no longer need to add a login name to the new instance. (Ps:alawayson does not have a sync login name and job.) This can solve the problem of the login name)

Disadvantage: You can only access your own database, and you cannot access other databases on the same instance. (PS: Bo Master failed to access the success, not definitely cannot access)

Steps to use:

Instance support:

　　　　　

EXEC sys.sp_configure n'contained database authentication', n'1  'GORECONFIGURE with OVERRIDEGo

　

The containing type of the new or modified database is partial.

Create Database DBName Containment=Partial

Alter DataBase Set Containment=Partial

When you modify a database, you also need to convert the database user that is mapped to the SQL Server login to the containing database user with the password.

Using System stored procedures:

sp_migrate_user_to_contained[@username =]N'User' ,       [@rename =]{N'Copy_login_name' |N'Keep_name' } ,       [@disablelogin =]{N'Disable_login' |N'Do_not_disable_login'}

Reference code:

DECLARE @usernamesysname; DECLAREUser_cursorCURSOR       for           SELECTDp.name fromSys.database_principals asDPJOINSys.server_principals asSP onDp.sid=Sp.sidWHEREDp.authentication_type= 1  andSp.is_disabled= 0; OPENUser_cursorFETCH NEXT  fromUser_cursor into @username       while @ @FETCH_STATUS = 0      BEGIN          EXECUTEsp_migrate_user_to_contained@username = @username,          @rename =N'Keep_name',          @disablelogin =N'Disable_login'; FETCH NEXT  fromUser_cursor into @username      END  CLOSEUser_cursor; deallocateUser_cursor;

Reference URL

Https://msdn.microsoft.com/zh-cn/library/ff929275.aspx

Specify the database name when connecting. In the connection string, you need to specify the initial catalog or database

SQL Server Certified Inclusion database