主页 / dba / 问题 / 62001
Accepted
Mark
Asked: 2014-03-29 06:49:14 +0800 CST

如何在删除之前找出是否有任何东西正在作为或依赖于用户帐户运行？

  • 772

运行 SQL Server 2008。

因此，我需要设法删除旧用户帐户，但我担心如果在该帐户下设置和/或以任何方式依赖它，则立即禁用或删除它可能会导致某些东西停止正常工作.

有没有我可以运行的脚本，它会告诉我如果我删除特定用户帐户是否有任何可能会破坏的东西？

sql-server-2008 security

1 个回答

  1. Best Answer

    我不认为有一种万无一失的方法可以找到所有东西。毕竟，他们可以仅仅凭借处于特定服务器或数据库角色，甚至是 Windows AD 组（您没有指定这是 SQL 身份验证登录还是 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;

    我确信还有其他可以添加到列表中的内容 - 如果您告诉我这些内容是什么，我可以毫无问题地帮助找出查询。

    • 4

相关问题