在刪除之前如何確定是否有任何東西作為使用者帳戶執行或依賴於使用者帳戶？

執行 SQL Server 2008。

所以我需要解決刪除舊使用者帳戶，但我擔心如果它們是在該帳戶下設置和/或以任何方式依賴它，立即禁用或刪除它可能會導致某些事情停止正常工作.

是否有我可以執行的腳本來告訴我如果我擺脫了特定的使用者帳戶，是否有任何可能破壞的東西？

我認為沒有萬無一失的方法可以找到所有內容。畢竟，他們可以僅通過處於特定伺服器或數據庫角色，甚至是 Windows AD 組（您沒有指定這是 SQL auth 登錄還是 Windows 登錄）來訪問事物。也沒有一種萬無一失的方法來確定如果更改可能會破壞什麼 - 例如，如果他們是作業或數據庫的所有者，將其更改為不同的所有者會導致任何問題嗎？也許，也許不是。我可以想到一些很容易找到的東西，但實際上沒有任何方法可以驗證它們不會在不實際更改它並查看損壞的情況下不會損壞。

對於最初的發現，這絕不是詳盡無遺的（只是我腦海中最常見的事情的開始）。請注意，為了便於閱讀，我已將它們分成塊，但您需要一次全部觸發它們以重用開始時聲明的變數。

SET NOCOUNT ON;
GO

USE [master];
GO

DECLARE @name NVARCHAR(128) = N'some login name',
---------- change this ---------^^^^^^^^^^^^^^^
       @sid VARBINARY(85),
       @pid INT,
       @sql NVARCHAR(MAX) = N'';

-- figure out their sid and *server* principal_id
SELECT @sid = [sid], @pid = principal_id 
 FROM sys.server_principals 
 WHERE name = @name;

– 連結伺服器登錄

SELECT [linked_servers] = COALESCE(remote_name, 
 N'<<LOCAL>> (' + @@SERVERNAME + ')')
 FROM sys.linked_logins
 WHERE local_principal_id = @pid;

– 具有授權權限的數據庫

SELECT [databases_owned] = name 
 FROM sys.databases WHERE owner_sid = @sid;

– 此登錄名擁有的工作

SELECT [jobs_owned] = name 
 FROM msdb.dbo.sysjobs 
 WHERE owner_sid = @sid;

– 具有此登錄名的作業作為步驟執行

SELECT [jobs_with_steps_having_login_as_proxy] = j.name
 FROM msdb.dbo.sysjobs AS j
 INNER JOIN msdb.dbo.sysjobsteps AS s
 ON j.job_id = s.job_id
 INNER JOIN msdb.dbo.sysproxylogin AS proxy
 ON s.proxy_id = proxy.proxy_id
 WHERE proxy.[sid] = @sid
 GROUP BY j.name;

– 他們是成員或所有者的伺服器角色

SELECT [member_or_owner_of_server_roles] = r.name
 FROM sys.server_role_members AS rm
 INNER JOIN sys.server_principals AS p
 ON p.principal_id = rm.member_principal_id
 INNER JOIN sys.server_principals AS r
 ON r.principal_id = rm.role_principal_id
 WHERE p.[sid] = @sid OR r.owning_principal_id = @pid;

– 已被授予或撤銷的伺服器權限

SELECT [server_permission] = [permission_name], state_desc
 FROM sys.server_permissions
 WHERE grantee_principal_id = @pid;

– 數據庫中的角色

SELECT @sql = N'';

SELECT @sql += ' UNION ALL 
SELECT [database] = ''' + name + ''', 
[member_or_owner_of_database_roles] = r.name
 FROM ' + QUOTENAME(name) + '.sys.database_role_members AS rm
 INNER JOIN ' + QUOTENAME(name) + '.sys.database_principals AS p
 ON p.principal_id = rm.member_principal_id
 INNER JOIN ' + QUOTENAME(name) + '.sys.database_principals AS r
 ON r.principal_id = rm.role_principal_id
 WHERE p.[sid] = @sid OR r.owning_principal_id = 
   (SELECT principal_id FROM ' + QUOTENAME(name) 
   + '.sys.database_principals WHERE sid = @sid)'
 FROM sys.databases 
 WHERE [state] = 0;

SELECT @sql = STUFF(@sql, 1, 10, '');

EXEC sp_executesql @sql, N'@sid VARBINARY(85)', @sid;

– 數據庫中的權限

SELECT @sql = N'';

SELECT @sql += N' UNION ALL 
SELECT 
 [database] = ''' + QUOTENAME(name) + ''', 
 [permission] = state_desc 
    + '' '' + [permission_name],
 [object] = COALESCE(QUOTENAME(s.name) 
   + ''.'' + QUOTENAME(o.name) + '' ('' 
   + RTRIM(o.[type] COLLATE SQL_Latin1_General_CP1_CI_AS) 
   + '')'', p.class_desc)
FROM ' + QUOTENAME(name) + '.sys.database_permissions AS p
INNER JOIN ' + QUOTENAME(name) + '.sys.database_principals AS dp
ON p.grantee_principal_id = dp.principal_id
LEFT OUTER JOIN ' + QUOTENAME(name) + '.sys.objects AS o
ON p.major_id = o.[object_id]
LEFT OUTER JOIN ' + QUOTENAME(name) + '.sys.schemas AS s
ON o.[schema_id] = s.[schema_id]
WHERE dp.[sid] = @sid'
 FROM sys.databases 
 WHERE [state] = 0;

SELECT @sql = STUFF(@sql, 1, 10, '');

EXEC sp_executesql @sql, N'@sid VARBINARY(85)', @sid;

– 數據庫中的模組與 EXECUTE AS

SELECT @sql = N'';

SELECT @sql += N' UNION ALL 
SELECT 
 [database] = ''' + QUOTENAME(name) + ''', 
 [object_with_execute_as] = QUOTENAME(s.name) 
   + ''.'' + QUOTENAME(o.name) + '' ('' 
   + RTRIM(o.[type] COLLATE SQL_Latin1_General_CP1_CI_AS) 
   + '')''
FROM ' + QUOTENAME(name) + '.sys.sql_modules AS m
INNER JOIN ' + QUOTENAME(name) + '.sys.database_principals AS dp
ON m.execute_as_principal_id = dp.principal_id
INNER JOIN ' + QUOTENAME(name) + '.sys.objects AS o
ON m.[object_id] = o.[object_id]
INNER JOIN ' + QUOTENAME(name) + '.sys.schemas AS s
ON o.[schema_id] = s.[schema_id]
WHERE dp.[sid] = @sid'
 FROM sys.databases 
 WHERE [state] = 0;

SELECT @sql = STUFF(@sql, 1, 10, '');

EXEC sp_executesql @sql, N'@sid VARBINARY(85)', @sid;

– 登錄“擁有”的數據庫中的對象（ALTER AUTHORIZATION）

SELECT @sql = N'';

SELECT @sql += N' UNION ALL 
SELECT 
 [database] = ''' + QUOTENAME(name) + ''', 
 [object_with_auth] = QUOTENAME(s.name) 
   + ''.'' + QUOTENAME(o.name) + '' ('' 
   + RTRIM(o.[type] COLLATE SQL_Latin1_General_CP1_CI_AS) 
   + '')''
FROM ' + QUOTENAME(name) + '.sys.database_principals AS dp
INNER JOIN ' + QUOTENAME(name) + '.sys.objects AS o
ON o.[principal_id] = dp.[principal_id]
INNER JOIN ' + QUOTENAME(name) + '.sys.schemas AS s
ON o.[schema_id] = s.[schema_id]
WHERE dp.[sid] = @sid'
 FROM sys.databases 
 WHERE [state] = 0;

SELECT @sql = STUFF(@sql, 1, 10, '');

EXEC sp_executesql @sql, N'@sid VARBINARY(85)', @sid;

– 登錄“擁有”的數據庫中的模式

SELECT @sql = N'';

SELECT @sql += N' UNION ALL
SELECT 
 [database] = ''' + QUOTENAME(name) + ''', 
 [schemas_owned] = QUOTENAME(s.name)
FROM ' + QUOTENAME(name) + '.sys.schemas AS s
INNER JOIN ' + QUOTENAME(name) + '.sys.database_principals AS dp
ON s.principal_id = dp.principal_id
WHERE dp.[sid] = @sid'
 FROM sys.databases 
 WHERE [state] = 0;

SELECT @sql = STUFF(@sql, 1, 10, '');

EXEC sp_executesql @sql, N'@sid VARBINARY(85)', @sid;

我確信還有其他可以添加到列表中 - 如果你告訴我這些東西是什麼，我可以幫助找出查詢。

引用自：https://dba.stackexchange.com/questions/62001