Skip to content
No results
  • About Me (Resume)
  • Meetup Note/Share
  • เงินทอง งอกเงย
naiwaen@DebuggingSoft วงแหวนเว็บ
  • About Me (Resume)
  • Meetup Note/Share
  • เงินทอง งอกเงย
naiwaen@DebuggingSoft วงแหวนเว็บ
Microsoft SQL Server

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

  • AdminpingAdminping
  • September 8, 2021
  • MS SQL Server

จาก 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 มาได้อย่างไร
    • - วิธีแรก: ใช้ Microsoft SQL Server Management Studio มันสร้างให้
    • - วิธีสอง: เขียน Script ขึ้นมาจากจาก คำสั่ง CREATE LOGIN
  • PASSWORD HASH
  • SID มาจากไหน ?
  • Final Script

สร้าง 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
จากภาพมาหมดเลยครับ ทั้ง sid และ password_hash
  • แต่ถ้าดูเฉพาะบาง 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

Share this:

  • Tweet
  • Click to email a link to a friend (Opens in new window) Email
  • Click to print (Opens in new window) Print
  • Click to share on Telegram (Opens in new window) Telegram
  • Click to share on WhatsApp (Opens in new window) WhatsApp

Like this:

Like Loading...

Related


Discover more from naiwaen@DebuggingSoft

Subscribe to get the latest posts sent to your email.

Tags
# Authorization# MS SQL Server# Restore# Permission# role member# Script Create Login# MSSQL# SQL Server

About me

Adminping

Adminping

My name is Chatri Ngambenchawong and I am a Software Developer, Blogger, Geek and FoodMania ^__^

View Full Profile →

Donate (ร่วมสนับสนุน)

Facebook Page

Facebook Page

Categories

Archives

Pages

  • About Me (Resume)
  • Meetup Note/Share
  • นโยบายการใช้คุกกี้ (Cookies Policy)
  • นโยบายความเป็นส่วนตัวของข้อมูล (Privacy Policy)
  • เงินทอง งอกเงย

Recent Posts

  • แยกร่างมาฟัง Global Azure 2025 Thailand + AzureSecEve
  • [CR] Starbucks Chicken Club Sandwich
  • เวลามี Merge Request / Pull Request มีแนวทางการตรวจยังไง
  • Spring Test Error java.lang.ClassNotFoundException: org.junit.platform.engine.reporting.OutputDirectoryProvider
  • [CR] Starbucks Spring Onion & Cream Cheese Bagel

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org

Related Posts

DB2 Data Type DECFLOAT แล้ว SQL Server ใช้อะไร ?

  • November 3, 2023

[MSSQL] สรุปงาน SQL Server Community Thailand Meeting #16

  • July 20, 2022

[MSSQL] สรุปงาน SQL Server Community Thailand Meeting #14

  • November 20, 2021

ถ้าสนใจ Blog สรุปต่างๆ ลองมา Subscribe กันได้ครับ เดี๋ยวจะมีเมล์จาก donotreply@wordpress.com มาให้กด Confirm อีกทีครับ

  • About Me (Resume)
  • นโยบายการใช้คุกกี้ (Cookies Policy)
Copyright © 2025 - WordPress Theme by CreativeThemes
Manage Cookie Consent
To provide the best experiences, we use technologies like cookies to store and/or access device information. Consenting to these technologies will allow us to process data such as browsing behavior or unique IDs on this site. Not consenting or withdrawing consent, may adversely affect certain features and functions.
Functional Always active
The technical storage or access is strictly necessary for the legitimate purpose of enabling the use of a specific service explicitly requested by the subscriber or user, or for the sole purpose of carrying out the transmission of a communication over an electronic communications network.
Preferences
The technical storage or access is necessary for the legitimate purpose of storing preferences that are not requested by the subscriber or user.
Statistics
The technical storage or access that is used exclusively for statistical purposes. The technical storage or access that is used exclusively for anonymous statistical purposes. Without a subpoena, voluntary compliance on the part of your Internet Service Provider, or additional records from a third party, information stored or retrieved for this purpose alone cannot usually be used to identify you.
Marketing
The technical storage or access is required to create user profiles to send advertising, or to track the user on a website or across several websites for similar marketing purposes.
Manage options Manage services Manage {vendor_count} vendors Read more about these purposes
View preferences
{title} {title} {title}
%d