گاهی یک دیتابیس را از یک SQL Server به سرور دیگری منتقل میکنید، Restore میکنید یا حتی Loginهای یک سرور را دوباره ایجاد میکنید؛ همهچیز ظاهراً درست است، اما ناگهان یک کاربر نمیتواند به دیتابیس متصل شود.
نکته جالب اینجاست که ممکن است کاربر داخل دیتابیس وجود داشته باشد، Permissionهای لازم را هم داشته باشد، اما SQL Server همچنان اجازه ورود ندهد.
یکی از دلایل رایج این اتفاق، Orphaned User است.
در این مقاله بررسی میکنیم Orphaned User دقیقاً چیست، چرا ایجاد میشود، چطور آن را پیدا کنیم و مهمتر از همه، در هر سناریو چگونه آن را بدون آسیب زدن به ساختار امنیتی دیتابیس برطرف کنیم.
Orphaned User دقیقاً یعنی چه؟
در SQL Server دو مفهوم را باید از هم جدا کنیم:
-
Login در سطح Instance قرار دارد.
-
User در سطح Database قرار دارد.
برای مثال ممکن است روی SQL Server یک Login با نام زیر داشته باشیم:
CREATE LOGIN [sqluser] WITH PASSWORD = 'StrongPassword';
و داخل دیتابیس نیز یک User برای آن Login وجود داشته باشد:
CREATE USER [sqluser] FOR LOGIN [sqluser];
در حالت عادی، SQL Server این دو را از طریق یک شناسه امنیتی یا SID به یکدیگر مرتبط میکند.
مشکل زمانی ایجاد میشود که User داخل دیتابیس وجود داشته باشد، اما Login متناظر آن در Instance وجود نداشته باشد یا SID این دو با یکدیگر یکسان نباشد.
در این شرایط با یک Orphaned User مواجه هستیم.
چرا Orphaned User ایجاد میشود؟
این مشکل معمولاً زمانی خودش را نشان میدهد که دیتابیس بین SQL Serverهای مختلف جابهجا شده باشد.
برای مثال فرض کنید دیتابیس SalesDB روی Server A قرار دارد و کاربر زیر به آن دسترسی دارد:
User: sqluserحالا از دیتابیس Backup گرفته و آن را روی Server B Restore میکنیم.
دیتابیس و User داخل آن منتقل میشوند؛ اما Loginها بخشی از Backup دیتابیس نیستند و در سطح Instance مدیریت میشوند.
بنابراین ممکن است روی Server B اصلاً Login زیر وجود نداشته باشد:
sqluser
حتی اگر Login را دوباره با همان نام ایجاد کنید، باز هم ممکن است مشکل حل نشود.
دلیل این موضوع SID است.
ممکن است وضعیت به این شکل باشد:
0xA1B2C3D4...Server Login SID0x98765432...نامها یکی هستند، اما SID متفاوت است.
برای SQL Server این دو Security Principal یکسان نیستند.
اول مطمئن شویم واقعاً Orphaned User داریم
قبل از اینکه شروع به تغییر Login و User کنیم، بهتر است ابتدا وضعیت را بررسی کنیم.
یکی از روشهای مناسب، بررسی مستقیم sys.database_principals و sys.server_principals است.
GOSELECTdp.name AS DatabaseUser,dp.type_desc AS UserType,dp.sid AS DatabaseSID,sp.name AS ServerLogin,sp.sid AS ServerSIDFROM sys.database_principals AS dpLEFT JOIN sys.server_principals AS spON dp.sid = sp.sidWHERE dp.type IN ('S', 'U', 'G')AND dp.sid IS NOT NULLAND sp.sid IS NULLORDER BY dp.name;اگر نتیجهای برگردد، یعنی User داخل دیتابیس وجود دارد اما Login متناظر آن در سطح Instance پیدا نشده است.
این دقیقاً یکی از نشانههای اصلی Orphaned User است.
یک روش قدیمیتر هم وجود دارد
در SQL Serverهای قدیمیتر معمولاً از sp_change_users_login برای پیدا کردن Orphaned User استفاده میشد:
GOEXEC sp_change_users_login 'Report';این روش هنوز در برخی محیطها دیده میشود، اما برای کارهای جدید بهتر است روی ALTER USER و Viewهای سیستمی تکیه کنید.
sp_change_users_login یک روش قدیمی و Deprecated محسوب میشود و بهتر است در طراحیهای جدید از آن استفاده نشود.
حالا برویم سراغ حل مشکل
بعد از اینکه Orphaned User را پیدا کردیم، سؤال اصلی این است:
Login موردنظر روی SQL Server وجود دارد یا نه؟
پاسخ به همین سؤال مشخص میکند چه کاری باید انجام دهیم.
سناریو اول: Login وجود دارد اما SID اشتباه است
فرض کنید این User را پیدا کردهایم:
Database User: sqluser
و روی Instance نیز Login زیر وجود دارد:
sqluser
اما SID آنها با یکدیگر مطابقت ندارد.
در این حالت نیازی نیست User را حذف کنیم.
کافی است User دیتابیس را به Login صحیح متصل کنیم:
GOALTER USER [sqluser]WITH LOGIN = [sqluser];این دستور باعث میشود User موجود در دیتابیس به Login موجود در Instance Map شود.
یکی از مزیتهای مهم این روش این است که User را حذف و دوباره ایجاد نمیکنیم؛ بنابراین Permissionهای Database User نیز حفظ میشوند.
سناریو دوم: Login اصلاً وجود ندارد
گاهی اوقات Login مربوطه واقعاً روی SQL Server مقصد وجود ندارد.
در این شرایط ابتدا باید مشخص کنیم Login مربوط به چه نوع Authentication بوده است.
اگر Login از نوع SQL Authentication باشد، میتوان Login را با SID مناسب ایجاد کرد.
ابتدا SID User را پیدا کنید:
GOSELECTname,type_desc,sidFROM sys.database_principalsWHERE name = 'sqluser';سپس میتوان Login را با همان SID ایجاد کرد:
WITHPASSWORD = 'YourStrongPassword',SID = 0x...,CHECK_POLICY = ON;در این حالت Login جدید دقیقاً همان SID مربوط به User دیتابیس را خواهد داشت و ارتباط بین آنها برقرار میشود.
اگر Login از نوع Windows باشد چه؟
در محیطهای Domain معمولاً Loginها از نوع Windows یا Windows Group هستند.
مثلاً:
FROM WINDOWS;اما در این سناریو نباید صرفاً به اسم Login نگاه کنید.
مهم این است که Windows SID و SID موجود در Database User با یکدیگر مطابقت داشته باشند.
به همین دلیل در محیطهای Active Directory، هنگام جابهجایی دیتابیس و Loginها باید وضعیت Domain Account و SIDها نیز بررسی شود.
آیا میتوانیم User را حذف کنیم؟
بله، اما حذف User باید آخرین گزینه باشد؛ نه اولین راهحل.
اگر مطمئن هستید User دیگر مورد استفاده نیست، میتوانید آن را حذف کنید:
GODROP USER [sqluser];اما قبل از اجرای این دستور، چند نکته مهم وجود دارد.
ممکن است User مالک یک Schema باشد یا Owner برخی Database Objectها باشد.
برای مثال:
↓sqluserدر چنین شرایطی DROP USER ممکن است با خطا مواجه شود.
بنابراین قبل از حذف User، Ownership و Dependencyهای آن را بررسی کنید.
یک اشتباه رایج بین DBAها
یکی از اشتباهات رایج این است که تصور کنیم:
«اسم Login و User یکی است، پس حتماً به هم متصل هستند.»
این تصور همیشه درست نیست.
برای SQL Server، SID مهمتر از Name است.
ممکن است این دو را داشته باشیم:
Name: sqluserSID: 0x1111Server LoginName: sqluserSID: 0x2222از دید ما نامها یکی هستند.
اما SQL Server این دو را یک Security Principal در نظر نمیگیرد.
بنابراین هنگام بررسی Orphaned User همیشه SID را هم بررسی کنید.
یک Query کاربردی برای بررسی وضعیت Userها
اگر به عنوان DBA میخواهید وضعیت Userهای یک دیتابیس را سریع بررسی کنید، Query زیر اطلاعات مفیدی در اختیار شما قرار میدهد:
GOSELECTdp.name AS DatabaseUser,dp.type_desc AS DatabaseUserType,dp.authentication_type_desc,dp.sid AS DatabaseSID,sp.name AS ServerLogin,sp.type_desc AS ServerLoginType,sp.sid AS ServerSID,CASEWHEN sp.sid IS NULL THEN 'ORPHANED'WHEN dp.sid = sp.sid THEN 'OK'ELSE 'SID MISMATCH'END AS UserStatusFROM sys.database_principals AS dpLEFT JOIN sys.server_principals AS spON dp.sid = sp.sidWHERE dp.type IN ('S', 'U', 'G')AND dp.sid IS NOT NULLORDER BYCASEWHEN sp.sid IS NULL THEN 1WHEN dp.sid <> sp.sid THEN 2ELSE 3END,dp.name;با این Query میتوانیم سریعتر متوجه شویم کدام Userها وضعیت مناسبی دارند و کدامیک نیاز به بررسی دارند.
Orphaned User را با Orphaned Login اشتباه نگیریم
یک نکته مهم دیگر این است که Orphaned User الزاماً به معنی خراب بودن Login نیست.
ممکن است Login کاملاً سالم باشد، اما User داخل دیتابیس به آن Login متصل نباشد.
بنابراین بهتر است همیشه این سه مورد را جداگانه بررسی کنیم:
↓SID↓Database User↓Database Permissionsمشکل در هر کدام از این بخشها میتواند نتیجه متفاوتی داشته باشد.
بهترین روش برای محیطهای Production
در محیط Production پیشنهاد میشود قبل از هر تغییر، ابتدا وضعیت فعلی را ثبت کنید.
مثلاً:
dp.name,dp.type_desc,dp.sidFROM sys.database_principals AS dpWHERE dp.type IN ('S', 'U', 'G')ORDER BY dp.name;همچنین Loginهای Instance را بررسی کنید:
name,type_desc,sidFROM sys.server_principalsWHERE type IN ('S', 'U', 'G')ORDER BY name;بعد از آن مشخص کنید مشکل دقیقاً کجاست.
اگر Login وجود دارد:
WITH LOGIN = [LoginName];اگر Login وجود ندارد، ابتدا Login مناسب را ایجاد کنید.
و فقط زمانی که User واقعاً دیگر مورد نیاز نیست، سراغ:
DROP USER [UserName];
بروید.
نکته مهم برای Migration و Restore
اگر مرتباً دیتابیسها را بین SQL Serverهای مختلف منتقل میکنید، موضوع Login و SID را جدی بگیرید.
Backup دیتابیس شامل ساختار Database و Userهاست، اما Loginهای Server-Level در Backup دیتابیس قرار نمیگیرند.
به همین دلیل یک فرآیند Migration حرفهای فقط شامل این موارد نیست:
↓Restore↓Doneبلکه باید Security Principalها نیز بررسی شوند:
+Database Users+Server Logins+SID Mapping+Database Roles+Permissionsاین موضوع مخصوصاً در Migration، DR، ایجاد محیطهای Test و انتقال دیتابیس بین سرورها اهمیت زیادی دارد.
جمعبندی
Orphaned User معمولاً زمانی ایجاد میشود که ارتباط بین Database User و Server Login از بین رفته باشد؛ رایجترین علت نیز انتقال یا Restore دیتابیس روی یک SQL Server دیگر و عدم تطابق SIDهاست.
برای رفع مشکل، اول باید مشخص کنیم Login مربوطه وجود دارد یا خیر.
اگر Login وجود دارد، معمولاً بهترین راهکار این است:
WITH LOGIN = [LoginName];اگر Login وجود ندارد، باید Login مناسب ایجاد شود و در سناریوهای لازم SID صحیح نیز در نظر گرفته شود.
و اگر User دیگر کاربردی ندارد، میتوان آن را حذف کرد؛ البته پس از بررسی Ownership و Dependencyهای آن.
در نهایت، مهمترین نکته این است که هنگام عیبیابی Orphaned User فقط به نام Login و User نگاه نکنید.
SID همان چیزی است که ارتباط واقعی بین این دو Security Principal را مشخص میکند.
💬 نظرات (0)
هنوز نظری ثبت نشده است. اولین نفری باشید که نظر میدهید!
📝 ثبت نظر جدید