SQL Server database authentication and inclusion database

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.