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