SQL Server database authentication and inclusion database

The first is SQL Server authentication and Windows identity authentication.

SQL Server authentication can run the following statement to query

1 Select *  from Sys.sql_logins

The administrator can modify the password directly, but cannot know the original password original, SQL server uses the obfuscation algorithm to protect the security less than Windows authentication,

Windows Authentication Mode

First divided into local accounts and domain accounts

SQL Server has distributed authentication and authorization to different objects, and SQL Server's login name is used for authentication, and SQL Server or Windows account must have a corresponding login name in SQL Server to successfully log in.

The user in each database is granted the appropriate permissions for the objects in the operational database. The login name is linked to the user through the SID, and the login name of the login SQL Server also gets the appropriate permissions for the operational database.

This mechanism brings the following two questions:

1. Improved maintenance costs for highly available solutions. MSDB (System database) cannot be mirrored. Similar to making a database mirroring system, you need to add the same user name password on both the principal and the mirror server, otherwise the mirror service cannot log in with the new login name if failover occurs. Also, when you add a login name to the mirror server, make sure that you use the same SID as the login name on the principal server, or the corresponding relationship between the login name and the database user will be compromised. Become the so-called orphan account.

2. Increased complexity of migrating databases. You cannot simply migrate user data databases and programs. Because there are also a subset of application-related objects that are missing from the user database, including the login name. When migrating an application, the login name needs to be extracted from the old environment separately and deployed to the new environment.

Isolate account resolution refer to the following links

Http://www.cnblogs.com/kerrycode/p/3465547.html

The premise is that the database compatibility level above 110, that is more than 2012. Include database creation:

1 EXEC sys.sp_configure n'contained database authentication', n'1  '2GO3RECONFIGURE with OVERRIDE4  GO

Modify [AdventureWorks2012] to include database

1  Use [master] 2 GO 3 ALTER DATABASE [AdventureWorks2012] SET =  with no_wait 4 GO

Querying all contained databases in an instance

1  Use Master 2 Select *  from sys.databases 3 where > 0

To change an existing database user to include a database user

1  Use [AdventureWorks2012]2 GO3 DECLARE @usernameSYSNAME;4 DECLAREUser_cursorCURSOR5  for6     SELECTDp.name7      fromSys.database_principals asDP8             JOINSys.server_principals asSp onDp.sid=Sp.sid9     WHEREDp.authentication_type= 1Ten              andSp.is_disabled= 0; One OPENUser_cursor A FETCH NEXT  fromUser_cursor into @username -  while @ @FETCH_STATUS = 0 -     BEGIN the         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;

SQL Server database authentication and inclusion database