[MSSQL] ตัวอย่าง Script ที่ช่วยในการสร้าง User หลัง Restore Database

Blog เขียนมา เพราะ หลักจากได้รับไม้ต่อ ให้มาดูเคสนึงครับ ซึ่งถ้าขึ้นไม่ได้เนี่ย ลูกค้าขอเลื่อนการจ่ายเงินออกไปอีกครับ โดยตอนแรกปัญหาที่ได้รับมาจากทีม CS คือ

• โปรแกรม Connector และ Excel-AddIns ใช้ไม่ได้เลยยย

พอเข้าไปผมเข้าไปเปิด Log4Net เป็นดูรายละเอียด Error ก็เจอว่า

เนื่องจากทาง CS ไม่สามารถแจ้งได้ว่า ตอนลงระบบเนี่ย เค้ามีขั้นตอนอย่างไร ทำให้การหาสาเหตุของปัญหายากมากครับ ผมเลยเสียเวลาไปกับการหาปัญหาของตัว Connector และ Excel-AddIns นานเลยครับ จนสุดท้ายลองเอา Tools ที่เคยทำมาทดสอบปรากฏว่าเป็นปัญหาที่ Database ครับ ไม่รู้ว่าใคร Restore ผิด?

• Set User DB ให้เป็น db_denydatawriter - User DB เขียนลง Table ไม่ได้
• ลืม Set User DB ให้เป็น db_owner ไม่เห็น Table เลย และปัญหาจุกจิอื่นๆครับ

สุดท้ายผม มา Refactor Script เพื่อจัดการกับสิทธิ์ครับ โดย Script นี้ผมลองทำขึ้นมาในช่วงปี 2012 เพราะ ขั้นตอนทำมือ โคตรเยอะ แล้วมี Refactor ตอนปี 2018 เลยมาก็แปะลง Blog เนี่ยแหละ เผื่อคนอื่นลองนำไปศึกษาครับ ผมใช้

• สิทธิ Windows Authentication ในการ Run ครับ
• Script ด้านล่างนี้ สร้าง และทดสอบบน MSSQL Server 2005 / 2008

--ตรวจสอบ User ก่อนว่า Instane มี User นี้ หรือป่าว ? ถ้าไม่ก็สร้าง
IF  NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'invest')
                CREATE LOGIN [invest] WITH PASSWORD = 0x0100E1BCBAF63A22EDDC4FCEE2551E32A45C51363F0A9AD4D95F HASHED, SID = 0x1B16028920ADF147BA9744E7CAE21EBF, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
GO
IF  NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'bonanza')
                CREATE LOGIN [bonanza] WITH PASSWORD = 0x01004D0ACBE8F6923518FBFCE9832985129E2ECB2A2B23DA99C3 HASHED, SID = 0x5AAEDC9C5626F345AD2052EE739879FE, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
GO
--////////////////////////////////////////////////////////
--จัดการ user ของ DB
--เอาสิทธิ์การเป็นเจ้าของ Schema INVEST ไปให้ dbo ก่อนเพื่อป้องกันปัญหาลบ User ไม่ได้ เพราะเป็นเจ้าของ Schema
ALTER AUTHORIZATION ON SCHEMA::INVEST TO dbo;
ALTER AUTHORIZATION ON SCHEMA::db_owner TO dbo;
--ลบ User ของ DB
DROP USER [invest];
DROP USER [bonanza];
--สร้าง User ใหม่
CREATE USER bonanza FOR LOGIN bonanza WITH DEFAULT_SCHEMA = INVEST;
CREATE USER invest FOR LOGIN invest WITH DEFAULT_SCHEMA = INVEST;
Go
--ใส่ Role ให้เป็น DB OWNER
EXEC sp_addrolemember 'db_owner', 'bonanza'
Go
EXEC sp_addrolemember 'db_owner', 'invest'
Go
--คืนสิทธิ์ให้เจ้าของ Schema Invest กลับไปให้ user invest
ALTER AUTHORIZATION ON SCHEMA::INVEST TO invest;

Reference

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