رفع خطای 15170 در SQL Server

رفع خطای 15170 در SQL Server: راهنمای جامع حذف Login با وابستگی‌ها

خطای 15170 در SQL Server یکی از چالش‌های رایج برای مدیران پایگاه داده (DBA) است که تلاش می‌کنند یک Login را از سرور حذف کنند. این خطا با پیام “Unable to drop login – Login owns objects or AG / permissions issue” نشان می‌دهد که عملیات حذف Login به دلیل وابستگی‌هایی که این Login در سرور یا پایگاه‌های داده دارد، یا مشکلات مربوط به مجوزها، امکان‌پذیر نیست. درک صحیح این خطا و روش‌های رفع آن برای حفظ امنیت، کارایی و سلامت پایگاه داده SQL Server ضروری است.

مدیریت Loginها و کاربران در SQL Server یک بخش حیاتی از مدیریت امنیتی است. Loginها هویت‌های سطح سرور هستند که به کاربران یا برنامه‌ها اجازه اتصال به نمونه SQL Server را می‌دهند، در حالی که کاربران هویت‌های سطح پایگاه داده هستند که Loginها را به یک پایگاه داده خاص نگاشت می‌کنند. هنگامی که یک Login دارای اشیاء خاصی (مانند پایگاه‌های داده، شمای‌ها، جداول، نماها، توابع، رویه‌های ذخیره شده) باشد یا بخشی از یک گروه در دسترس بودن (Availability Group) باشد، SQL Server به طور پیش‌فرض از حذف آن جلوگیری می‌کند تا از از بین رفتن یکپارچگی داده‌ها و عملکرد صحیح سیستم جلوگیری شود. این مقاله به صورت جامع به بررسی علل این خطا و ارائه راهکارهای عملی برای رفع آن می‌پردازد.

علل اصلی خطای 15170 در SQL Server

خطای 15170 می‌تواند به دلایل مختلفی رخ دهد که همگی به یک مسئله اساسی اشاره دارند: Login مورد نظر قبل از حذف، هنوز دارای مالکیت یا عضویت‌های مهمی در سرور یا پایگاه‌های داده است. درک این علل برای انتخاب رویکرد صحیح جهت رفع مشکل ضروری است:

1. Login مالک اشیاء پایگاه داده است: این شایع‌ترین علت است. یک Login می‌تواند مالک شمای‌ها (Schemas)، جداول، نماها (Views)، رویه‌های ذخیره شده (Stored Procedures)، توابع، انواع داده (User-Defined Types) و سایر اشیاء در یک یا چند پایگاه داده باشد. SQL Server اجازه نمی‌دهد Login مالک اشیاء حذف شود زیرا این کار منجر به یتیم شدن (Orphaning) این اشیاء و از بین رفتن دسترسی به آن‌ها می‌شود.
2. Login مالک پایگاه داده است: اگر Login مورد نظر به عنوان مالک یک یا چند پایگاه داده (Database Owner) تنظیم شده باشد، نمی‌توان آن را حذف کرد. مالک پایگاه داده دسترسی کامل به آن پایگاه داده را دارد و SQL Server از حذف ناخواسته این نقش جلوگیری می‌کند.
3. Login عضو یک گروه در دسترس بودن (Always On Availability Group) است: اگر Login بخشی از پیکربندی Always On Availability Group باشد، به عنوان مثال، به عنوان حساب سرویس (Service Account) برای SQL Server یا Endpoint Mirroring استفاده شود، حذف آن به طور مستقیم ممکن نیست. این وابستگی‌ها برای عملکرد صحیح AG حیاتی هستند.
4. Login عضو نقش‌های سرور (Server Roles) است: اگر Login مورد نظر عضو نقش‌های سرور (مانند sysadmin، securityadmin و غیره) باشد، اگرچه به ندرت به طور مستقیم منجر به خطای 15170 می‌شود، اما برای حفظ ساختار امنیتی سرور، بهتر است قبل از حذف، عضویت آن را از این نقش‌ها حذف کنید.
5. مشکلات مجوز (Permissions Issue): در برخی موارد نادر، مشکل می‌تواند به دلیل مجوزهای ناکافی کاربر تلاش‌کننده برای حذف Login باشد. برای حذف یک Login، کاربر باید دارای مجوز `ALTER ANY LOGIN` یا عضویت در نقش سرور `securityadmin` باشد.
6. کاربران یتیم (Orphaned Users): اگرچه کمتر به طور مستقیم باعث خطای 15170 می‌شود، اما اگر یک Login حذف شده باشد و سپس یک Login جدید با نام مشابه ایجاد شود، ممکن است کاربرانی در پایگاه داده‌ها وجود داشته باشند که هنوز به Login قدیمی ارجاع می‌دهند. این وضعیت می‌تواند پیچیدگی‌هایی در مدیریت Loginها ایجاد کند، اما عمدتاً به مشکلات اتصال کاربران مربوط می‌شود تا حذف Login.

برای رفع این خطا، لازم است قبل از تلاش برای حذف Login، تمامی وابستگی‌های شناسایی شده را از آن جدا کرده یا مالکیت آن‌ها را به یک Login دیگر منتقل کنیم.

راهکارهای عملی برای رفع خطای 15170

رفع خطای 15170 نیازمند شناسایی دقیق وابستگی‌ها و سپس انتقال مالکیت یا حذف عضویت‌ها به صورت گام به گام است. مراحل زیر یک رویکرد جامع را برای حل این مشکل ارائه می‌دهند:

گام 1: شناسایی وابستگی‌های Login

قبل از هر اقدامی، باید مشخص کنید Login مورد نظر مالک چه اشیاء یا منابعی است. این کار را باید برای هر پایگاه داده در سرور و همچنین در سطح سرور انجام دهید.

ابتدا، برای شناسایی پایگاه‌های داده‌ای که Login مورد نظر ممکن است مالک آنها باشد، می‌توانید از کوئری زیر استفاده کنید:

SELECT name AS DatabaseName, SUSER_SNAME(owner_sid) AS DatabaseOwner
FROM sys.databases
WHERE SUSER_SNAME(owner_sid) = 'YourLoginName';

در این کوئری، `YourLoginName` را با نام Login مورد نظر خود جایگزین کنید. این کوئری نام پایگاه داده و مالک آن را برای همه پایگاه‌های داده‌ای که Login شما مالک آنهاست، نمایش می‌دهد.

سپس، برای شناسایی اشیاء (مانند جداول، نماها، رویه‌ها) که توسط Login مورد نظر در یک پایگاه داده خاص (یا تمام پایگاه‌های داده) ایجاد شده‌اند، می‌توانید کوئری زیر را در context هر پایگاه داده اجرا کنید (یا یک حلقه برای تمام پایگاه‌های داده بنویسید):

USE [YourDatabaseName];
GO
SELECT
    o.name AS ObjectName,
    o.type_desc AS ObjectType,
    SCHEMA_NAME(o.schema_id) AS SchemaName,
    dp.name AS OwnerName
FROM sys.objects AS o
INNER JOIN sys.database_principals AS dp
    ON o.principal_id = dp.principal_id
WHERE dp.name = 'YourLoginName'
ORDER BY ObjectType, ObjectName;

این کوئری تمام اشیائی را که Login مورد نظر شما به صورت مستقیم مالک آنهاست (اگر `principal_id` شیء با `principal_id` کاربر نگاشت شده به Login در آن پایگاه داده مطابقت داشته باشد) در پایگاه داده `YourDatabaseName` نمایش می‌دهد. در اینجا نیز `YourLoginName` و `YourDatabaseName` را با مقادیر صحیح جایگزین کنید.

برای شناسایی شمای‌هایی که Login مالک آنهاست:

USE [YourDatabaseName];
GO
SELECT s.name AS SchemaName, p.name AS SchemaOwner
FROM sys.schemas AS s
INNER JOIN sys.database_principals AS p
    ON s.principal_id = p.principal_id
WHERE p.name = 'YourLoginName';

همچنین، بررسی کنید که آیا Login مورد نظر عضو هیچ Server Role‌ای است یا خیر:

SELECT
    roles.name AS ServerRole,
    members.name AS MemberLogin
FROM sys.server_role_members AS srm
JOIN sys.server_principals AS roles
    ON srm.role_principal_id = roles.principal_id
JOIN sys.server_principals AS members
    ON srm.member_principal_id = members.principal_id
WHERE members.name = 'YourLoginName';

این کوئری نقش‌های سروری را که Login شما در آن‌ها عضو است، نشان می‌دهد. در نهایت، اگر از Always On Availability Groups استفاده می‌کنید، باید بررسی کنید که آیا این Login به عنوان حساب سرویس یا در پیکربندی AG نقش دارد. این بررسی معمولاً از طریق تنظیمات AG در SSMS یا کوئری‌های مربوط به `sys.availability_groups` و `sys.availability_group_listeners` انجام می‌شود.

گام 2: انتقال مالکیت یا حذف عضویت‌ها

پس از شناسایی وابستگی‌ها، نوبت به انتقال مالکیت یا حذف عضویت‌ها می‌رسد. این مرحله حیاتی است و باید با دقت انجام شود تا از از دست دادن داده‌ها یا عملکرد سیستم جلوگیری شود.

انتقال مالکیت پایگاه داده (Database Ownership)

اگر Login مورد نظر مالک یک یا چند پایگاه داده است، باید مالکیت آن‌ها را به `sa` (System Administrator) یا یک Login مناسب دیگر (ترجیحاً یک گروه با حداقل دسترسی یا یک Login سرویس اختصاصی) منتقل کنید. `sa` معمولاً به عنوان مالک پیش‌فرض بسیاری از پایگاه داده‌های سیستمی عمل می‌کند، اما برای پایگاه داده‌های کاربر، یک Login مشخص و با حداقل مجوزهای لازم بهتر است. برای انتقال مالکیت، دستور زیر را اجرا کنید:

ALTER AUTHORIZATION ON DATABASE::[YourDatabaseName] TO [NewLoginName];

`YourDatabaseName` را با نام پایگاه داده و `NewLoginName` را با نام Login جدیدی که قرار است مالک باشد، جایگزین کنید. این کار را برای هر پایگاه داده‌ای که Login شما مالک آن است، تکرار کنید.

انتقال مالکیت شمای‌ها (Schema Ownership)

اگر Login مالک شمای‌هایی در یک پایگاه داده است، باید مالکیت آن‌ها را به یک کاربر دیگر (که به یک Login معتبر نگاشت شده باشد) منتقل کنید. شمای `dbo` معمولاً گزینه خوبی است، اما می‌توانید یک شمای جدید نیز ایجاد کرده و مالکیت را به آن منتقل کنید. دستور زیر را برای هر شمایی که توسط Login شما مالکیت شده است، اجرا کنید:

USE [YourDatabaseName];
GO
ALTER AUTHORIZATION ON SCHEMA::[YourSchemaName] TO [dbo];

`YourDatabaseName` و `YourSchemaName` را جایگزین کنید. در اینجا، `dbo` کاربر پیش‌فرض پایگاه داده است که معمولاً به Login `sa` نگاشت می‌شود.

انتقال مالکیت اشیاء پایگاه داده (Object Ownership)

اگر Login مالک اشیاء خاصی (مانند جداول، نماها، رویه‌ها) در یک شمای است، این اشیاء معمولاً از مالکیت شمای پیروی می‌کنند. اگر مالکیت شمای را تغییر دهید، اشیاء داخل آن نیز به مالک جدید شمای منتقل می‌شوند. با این حال، در موارد خاصی که اشیاء به صورت جداگانه مالکیت شده‌اند، ممکن است نیاز به انتقال مالکیت تک تک اشیاء باشد:

USE [YourDatabaseName];
GO
ALTER AUTHORIZATION ON OBJECT::[YourSchemaName].[YourObjectName] TO [NewUserName];

در این دستور، `YourDatabaseName`، `YourSchemaName`، `YourObjectName` و `NewUserName` را با مقادیر صحیح جایگزین کنید. `NewUserName` باید یک کاربر معتبر در آن پایگاه داده باشد که به یک Login موجود نگاشت شده است.

حذف Login از Server Roles

اگر Login مورد نظر عضو هر Server Role‌ای است، باید آن را حذف کنید. این کار به افزایش امنیت کمک می‌کند و در برخی موارد ممکن است برای حذف Login ضروری باشد:

ALTER SERVER ROLE [YourServerRoleName] DROP MEMBER [YourLoginName];

`YourServerRoleName` و `YourLoginName` را جایگزین کنید. این دستور را برای هر Server Role‌ای که Login شما در آن عضو است، اجرا کنید.

مدیریت Always On Availability Groups

اگر Login به عنوان بخشی از Always On Availability Group (مثلاً به عنوان حساب سرویس) استفاده می‌شود، حذف آن پیچیده‌تر است و ممکن است نیاز به بازسازی یا تغییر پیکربندی AG داشته باشد. این کار معمولاً شامل تغییر حساب سرویس نمونه‌های SQL Server یا تغییر Endpoint Mirroring در AG می‌شود. این اقدامات باید با دقت فراوان و با در نظر گرفتن تأثیر بر دسترس‌پذیری سیستم انجام شود.

گام 3: حذف کاربران پایگاه داده نگاشت شده

پس از انتقال مالکیت اشیاء و شمای‌ها، لازم است کاربران پایگاه داده را که به Login مورد نظر نگاشت شده‌اند، حذف کنید. این مرحله برای هر پایگاه داده که Login در آن یک کاربر دارد، باید انجام شود. قبل از حذف کاربران، حتماً تمام وابستگی‌های ذکر شده در بالا را برطرف کرده باشید.

USE [YourDatabaseName];
GO
DROP USER [YourUserName];

`YourDatabaseName` و `YourUserName` (که همان نام Login است اگر کاربر با نام Login ایجاد شده باشد) را جایگزین کنید. این دستور را برای تمام پایگاه‌های داده‌ای که کاربر مورد نظر در آن‌ها وجود دارد، اجرا کنید.

گام 4: حذف Login

پس از انجام تمامی مراحل فوق و اطمینان از اینکه Login هیچ وابستگی یا مالکیتی ندارد، می‌توانید آن را حذف کنید. این دستور را در سطح سرور اجرا کنید:

DROP LOGIN [YourLoginName];

اگر تمام وابستگی‌ها به درستی حذف یا منتقل شده باشند، این دستور باید با موفقیت اجرا شود و Login از SQL Server حذف گردد. اگر هنوز با خطای 15170 مواجه شدید، به این معنی است که یک یا چند وابستگی شناسایی نشده یا به درستی مدیریت نشده است. در این صورت، لازم است مراحل شناسایی و انتقال مالکیت را با دقت بیشتری تکرار کنید.

نکات تکمیلی و بهترین رویه‌ها

هنگام مدیریت Loginها و رفع خطای 15170، رعایت نکات زیر می‌تواند به شما کمک کند تا فرآیند را به صورت کارآمدتر و با امنیت بیشتر انجام دهید:

• ایجاد Login جایگزین معتبر: همیشه مطمئن شوید که یک Login معتبر و امن برای انتقال مالکیت‌ها وجود دارد. استفاده از Login `sa` به عنوان مالک برای تمام اشیاء توصیه نمی‌شود؛ بهتر است یک Login مدیریتی یا سرویس اختصاصی برای این منظور داشته باشید.
• مستندسازی: قبل از انجام هرگونه تغییر، وضعیت فعلی مالکیت‌ها و مجوزها را مستند کنید. این کار در صورت نیاز به بازگرداندن تغییرات یا بررسی‌های بعدی، مفید خواهد بود.
• تست در محیط غیرتولیدی: همیشه قبل از اعمال تغییرات در محیط تولید، آن‌ها را در یک محیط تست یا توسعه شبیه‌سازی کنید تا از بروز مشکلات ناخواسته جلوگیری شود.
• بررسی مجوزهای اجراکننده: اطمینان حاصل کنید Login که دستورات `ALTER AUTHORIZATION` یا `DROP LOGIN` را اجرا می‌کند، دارای مجوزهای کافی (مانند عضویت در نقش `sysadmin` یا `securityadmin`) باشد.
• استفاده از ابزارهای SQL Server Management Studio (SSMS): SSMS می‌تواند به شما در مشاهده مالکیت‌ها کمک کند. با راست کلیک بر روی یک پایگاه داده و انتخاب “Properties” و سپس “Files” می‌توانید مالک آن را مشاهده کنید. همچنین برای اشیاء می‌توانید در Object Explorer به بخش “Properties” هر شیء مراجعه کنید.

با رعایت این مراحل و نکات، می‌توانید به طور مؤثر خطای 15170 را در SQL Server رفع کرده و Login‌های ناخواسته یا قدیمی را حذف کنید، در حالی که یکپارچگی و امنیت داده‌های خود را حفظ می‌کنید. مدیریت صحیح Login‌ها و کاربران یک جزء کلیدی در حفظ سلامت و کارایی محیط‌های SQL Server است.