Chapter 2 User authentication, Authorization, and Security (2): Create login account

原文出处：http://blog.csdn.net/dba_huangzj/article/details/38705965，专题目录：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/38657111

Preface:

Login account is defined at the server (instance) level, and can be granted for administrative tasks, connections, access to the database and other permissions of the account. The SQL Server security model has two levels: server-level and database-level. The login account must first be created at the server level. If you need to access the database, you also need to create a user mapping to an existing login at the database level.

For the partial inclusion database introduced from SQL Server 2012 (partially contained Databases), the database can be accessed without a login at the server level, as described in later chapters.

implementation:

1. Open SSMs, connect to the corresponding instance, and then at server-level security, right-click "Login" and choose "New Login":

2. Enter login name, can be SQL Server account number, can also be a Windows account (< machine name or domain >\< account name >), if it is a Windows account, you can browse in the "find".

Original source: http://blog.csdn.net/dba_huangzj/article/details/38705965

3. If SQL Server authentication is selected, you can enter a password and a confirmation password, in some cases you can use a blank password (not recommended, of course), and the following are the options you need to explain: enforcing password policies (enforce password Policy): Enforces the use of Windows password policy, without this option, you can use a blank password, and the following two options will also be turned off. Force password expiration (enforce password expiration): Password expires according to Windows password policy. User must change password at next logon (user must changes password at next login): Users must modify the password when they first log on using the GUI.

4. At the bottom of the interface, you can choose the default database, if not the master library, as a best practice, it is recommended that you do not connect to the master library, and DBAs and developers may forget that switching the database causes some DDL/DML statements to run in the Master library. I am accustomed to selecting the database required by the user, or tempdb.

5. You can also set the default language, but this may cause errors and date formatting problems in a multilanguage environment.

6. In addition to using SSMS creation, you can also use T-SQL to create logins, which is an example:

--Create a Windows login account in a domain, domain name, and the account name is Fred 
Create login [domain\fred] from WINDOWS; 
--Create a SQL login account with the login named Fred 
Create login fred with PASSWORD = ' strong password ' must_change, 
check_expiration = ON, check_policy = ON, Default_database =adventureworks2012, default_language = us_english;

principle:

Password policies on Windows Server 2008 can refer to the article: http://technet.microsoft.com/en-us/library/cc264456.aspx

The password complexity, length, password history, minimum and maximum lifecycle, and locking parameters are defined in the policy. The default complexity is: cannot contain more than 2 consecutive characters that appear in the login name. The password is at least 6 characters long. Passwords need to contain at least 3 different types of characters in the following 4 characters: uppercase, lowercase, digits, and special symbols.

If you use the Windows user group as your logon name, you can use the following statement to check whether the current login belongs to a member of a group:

SELECT is_member (' domain name \ Group name ');--1 is a member of the corresponding group in the domain for the current user.

However, note that this function does not reflect the situation where the login name was changed to the SQL Server and the group member was modified.

MORE:

Original source: http://blog.csdn.net/dba_huangzj/article/details/38705965

When the SQL login uses the Check_policy option, the account may be locked because of some factors. You can use the following command to unlock:

--Unlock 
ALTER LOGIN fred with PASSWORD = ' new password ' UNLOCK by resetting the password;
--No need to reset the password: 
ALTER LOGIN fred with check_policy = off; 
--First close policy 
ALTER LOGIN fred with check_policy = on;--reopen Policy

Check the status of the login account:

You can use the LoginProperty function to check the status of a login: details can be accessed: http://msdn.microsoft.com/zh-cn/library/ms345412.aspx

Here are some examples:

DECLARE @login as SYSNAME = ' Fred '; 
SELECT  loginproperty (@login, ' BadPasswordCount ') as [bad Password Count], 
        loginproperty (@login, ' Badpasswordtime ') as [Password time], 
        loginproperty (@login, ' daysuntilexpiration ') as [Nb of days before exp Iration], 
        loginproperty (@login, ' historylength ') as [Nb of passwords in history], 
        LoginProperty (@login, ' IsExpired ') as [expired], 
        loginproperty (@login, ' islocked ') as [is locked], 
        loginproperty (@login, ' Passwordlastsettime ') as [Password last Set time];

To modify the SQL logon name password:

You can use the ALTER LOGIN login with password= ' new password ' to modify the password for the SQL login. Executing this password requires CONTROL server or alter ANY login permissions (for the sysadmin or securityadmin fixed role member, respectively). If you need to modify your password, you need to provide an old password, such as:

ALTER LOGIN fred with 
PASSWORD = ' My new complex PASSWORD ' 
old_password = ' I old complex PASSWORD ';

The password is an irreversible hash store, and if you want to check if the password is correct, you can use the Non-public function Pwdcompare:

Use master 
go 
Create login Fred with PASSWORD = ' admin!@ #123 '--Create a login go 
--Check the password for the login name 
SELECT Pwdcompare (' admin!@ #123 ', CAST (LoginProperty (' Fred ', ' PasswordHash ') as varbinary (256));--return 1 to prove the password is correct
SELECT Pwdcompare (' 123 ', CAST (LoginProperty (' Fred ', ' PasswordHash ') as varbinary (256));--return 0 to prove incorrect password

Original source: http://blog.csdn.net/dba_huangzj/article/details/38705965

to copy a SQL login between instances:

If you need to migrate servers, then you can't simply create the same account password on the new server, some applications need to use SID for authentication, and each account has a different SID, starting with SQL Server 2005, providing a set of scripts to implement the migration: http:// support.microsoft.com/kb/918992

In addition to scripting, you can use the "Transfer Logins task" of SSIS to do the following steps:

1. Open SQL Server Data Tools (2012 start renaming, before bids, but more powerful after 2012), create a new project, select Business Intelligence Group, and then click Integration Services Project. Enter a project name and click "OK":

2. Find "Transfer Logins task":

Original source: http://blog.csdn.net/dba_huangzj/article/details/38705965

3. Double-click the icon to open the Transfer Login Task Editor:

4. On the Login Name page, select the source and destination links:

5. In "Loginstotransfer" Select the login name you want to copy: alllogins: Transfer all SQL Server logins on the source server. Selectedlogins: Only the login name that exists in the "loginslist" option is transferred. Allloginsfromselecteddatabases: Transfers all the logins that map to the user that defines the database in "Databaseslist".

6. In the "ifobjectexists" option, define how to respond to logins that already exist for the target server: Failtask: Stop the task and play an error Overwrite: Overwrite the logon name of the target server and cause the user to become orphaned if the SID does not replicate. Skip: Skipping and continuing with other logins.

7. In the "copysids" option, you can choose to copy logins and keep the same SID, this step is recommended.

How to use SSIS tasks, you can refer to http://msdn.microsoft.com/zh-cn/library/ms188664.aspx (using the Copy Database Wizard)

To manually copy a password:

If you need to manually copy the password, you can use the Create login command to:

--Gets the hash value of the password, and the statement does not need to change the 
SELECT loginproperty (' Fred ', ' PasswordHash '); 
--Use the hash value obtained above to create a new login name, you need to manually paste the hash value created 
login fred with PASSWORD ' Paste the above obtained hash value ' hashed;

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