[MSSQL] อธิบาย Script ที่ช่วยในการสร้าง User จากคำสั่ง Create Login

จาก Blog ตอนก่อนมีเขียนเรื่อง [MSSQL] ตัวอย่าง Script ที่ช่วยในการสร้าง User หลัง Restore Database ทุกคนใช้มาเรื่อยๆ จนหลังๆ ทาง CS เริ่มมีนำไปใช้กับทางลูกค้าบ้างแล้ว (Script นี้ทำ และเสนอให้ใช้มาแล้ว 8 ปี เหอะๆ) มีข้อสงสัยไหนๆจะอธิบาย Script และเขียนลง Blog ดีกว่า เริ่มกันที่ Script

CREATE LOGIN login_name] WITH PASSWORD = 0x0100E1BCBAF63A22EDDC4FCEE2551E32A45C51363F0A9AD4D95F HASHED, SID = 0x1B16028920ADF147BA9744E7CAE21EBF, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
GO

สร้าง Script มาได้อย่างไร

ตัว Script สร้างได้ 2 แบบเลย ได้แก่

- วิธีแรก: ใช้ Microsoft SQL Server Management Studio มันสร้างให้

• คลิกตามขั้นตอน ดังรูปเลยครับ ระบบมันสร้าง Script มาให้

• ถ้าดูจาก Script ที่มันสร้างให้มา พบว่า
  • PASSWORD ยังไม่ Hash
  • SID มันขาดไป
  • มีเพิ่ม Statement ALTER LOGIN [your_user_name] DISABLE เพื่อปิดการใช้งาน user ที่สร้างจาก Script เป็น default

- วิธีสอง: เขียน Script ขึ้นมาจากจาก คำสั่ง CREATE LOGIN

• คำสั่ง CREATE LOGIN โดยผมขอสรุปที่สำคัญ ดังนี้ครับ
  • LOGIN : ชื่อ username
  • PASSWORD : รหัสผ่าน มี 2 แบบ ได้แก่ PLAIN ใส่ Pass เข้าไปตรงๆเลย หรือแบบ PASSWORD HASH ตรงนี้ขอเขียนแยกอีก Section
  • SID : unique id ของ User ตรงนี้ขอเขียนแยกอีก Section
  • DEFAULT_DATABASE : Default Database ของ User
  • CHECK_POLICY : เอาไว้ตรวจสอบว่า PASSWORD ที่เพิ่มขึ้นมา ตรงกับ Policy ที่กำหนดไว้ใน Server ไหม ?
  • CHECK_EXPIRATION : เอาไว้ตรวจสอบว่า PASSWORD หมดอายุ ตาม Policy ไหม ? ตรงนี้ผมจะกำหนดเป็น OFF

ส่วนตัวผมใช้วิธีแรก + วิธีที่สองครับ ให้มันช่วย Generate Script มาให้ แล้วมาแก้เป็น Script ที่ต้องการใช้งาน

PASSWORD HASH

• ทำไมต้อง HASH ตัว Password เพราะ เราไม่เสือก ไม่จำเป็นต้องรู้ Password ครับ (we do not need to know the original password)
• สิ่งที่ต้องทำ SQL Server ต้องรู้ ว่ายืนยันตัวต้องด้วยข้อมูลนี้นะ
• จึงเป็นที่มาของ PASSWORD HASH คนที่ Run Script ไปไม่สามารถเอา Password ไปแกะได้ง่ายๆครับ
• เอา PASSWOD HASH มาจากไหน
  • sys.sql_logins (Transact-SQL) - SQL Server | Microsoft Docs
  • LOGINPROPERTY (Transact-SQL) - SQL Server | Microsoft Docs
• ถ้าใช้ sys.sql_logins มันจะเห็นหมดเลย ตามตัวอย่าง Query

SELECT  SL.name
		, SL.principal_id
		, SL.sid
		, SL.type
		, SL.type_desc
		, SL.password_hash
		, SL.is_disabled
		, SL.create_date
		, SL.modify_date
		, SL.default_database_name
		, SL.default_language_name
		, SL.credential_id
		, SL.is_policy_checked
		, SL.is_expiration_checked
FROM sys.sql_logins AS SL

• แต่ถ้าดูเฉพาะบาง Property สามารถใช้ LOGINPROPERTY ได้ครับ

SELECT LOGINPROPERTY('invest','PasswordHash');

SID มาจากไหน ?

• ถึงแม้ว่า ใช้ Microsoft SQL Server Management Studio มันสร้าง Script ใช้ มันจะติดปัญหาไม่มี SID มาด้วย โดยตัว SID จุดประสงค์ของมันเหมือน unique id เพื่อบอกว่า user A ของเครื่อง Server 128.1.0.24 กับ user A ของ Server 128.1.0.18 เป็น คนเดียวกันนะ
• ถ้า SID ไม่เหมือนกัน มันจะเกิดปัญหาอะไรบ้าง ส่วนใหญ่เป็นเรื่องของ Authentication/Authorization
  • Restore DB ที่เครื่องอื่น : ไม่งั้นต้องมาสร้าง user ใหม่หมด
  • Log Shipping / AlwaysOn / Database Mirroring : ไม่งั้นจะเกิดปัญหาที่ Application ตอน switch เครื่อง
• SID ดูได้อย่างไร โดยดูจาก SQL ด้านล่าง โดยเทียบ SID ของตัว DB ของ App เรา กับ DB ของ MS SQL SERVER เอง ซึ่งต้องมีค่าเหมือนกัน

USE <<YOUR DB NAME>>
GO 
SELECT name, sid FROM sys.sysusers 
WHERE name = 'invest' 
GO 

USE MASTER 
GO 
SELECT name, sid FROM sys.sql_logins 
WHERE name = 'invest' 
GO

Final Script

• หลังจากได้ SID/Password Hash แล้ว Copy นำค่าไปใส่ Script Create Login ได้เลยครับ ได้ Script ที่สวยงามแล้วครับ

CREATE LOGIN [invest] WITH PASSWORD = 0x0100E1BCBAF63A22EDDC4FCEE2551E32A45C51363F0A9AD4D95F HASHED, SID = 0x1B16028920ADF147BA9744E7CAE21EBF, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
GO

Reference

• CREATE LOGIN (Transact-SQL) - SQL Server | Microsoft Docs
• LOGINPROPERTY (Transact-SQL) - SQL Server | Microsoft Docs