SQL - 循环访问数据库列表，为孤立用户返回单个结果

Question

我正在使用下面的代码为我提供一个数据库的孤立用户列表，该数据库运行良好。我现在正在尝试将其构建为将循环访问数据库列表并在单个表中返回结果的东西。

这是我用来获取孤立用户的代码：

select DB_NAME() AS [Current Database], u.uid, u.name, u.sid, rm.role_principal_id as 'Role ID', dp1.name as 'Role'
from sys.sysusers u
left join sys.syslogins l on UPPER(u.sid) = UPPER(l.sid)
inner join sys.database_role_members rm on rm.member_principal_id = u.uid
left join sys.database_principals dp on dp.principal_id = rm.member_principal_id
left join sys.database_principals dp1 on dp1.principal_id = rm.role_principal_id 
where u.uid > 4 and u.issqlrole = 0
--and issqluser = 1 --commented out to include orphaned windows logins
and l.name is null
order by 1

我可以使用下面的数据库列表，但我想与上面的结合来依次浏览每个数据库。

SELECT name FROM sys.databases 
WHERE database_id > 4

任何帮助或指示将不胜感激！

谢谢 ：-）

Answer 1

可以使用系统存储过程sp_msforeachdb对每个数据库运行一组 SQL 语句。在这种情况下，该过程的每次执行都将返回一个单独的结果集，因此我们使用临时表将所有结果收集到单个表中。然后，您可以使用普通 SQL 操作此单个表来过滤和排序最终结果。

IF OBJECT_ID('tempdb..#Orphans') IS NOT NULL
  DROP TABLE #Orphans
CREATE TABLE #Orphans 
(
  [Current Database] SYSNAME,
  [uid]              SMALLINT,
  [name]             SYSNAME,
  [sid]              VARBINARY(85),
  [Role ID]          SMALLINT,
  [Role]             SYSNAME
)
INSERT #Orphans
EXEC sp_msforeachdb 'use [?];
    select DB_NAME() AS [Current Database]
         , u.uid, u.name
         , u.sid
         , rm.role_principal_id as [Role ID]
         , dp1.name as [Role]
      from sys.sysusers u
             left join sys.syslogins l 
               on UPPER(u.sid) = UPPER(l.sid)
                 inner join sys.database_role_members rm 
                   on rm.member_principal_id = u.uid
                     left join sys.database_principals dp 
                       on dp.principal_id = rm.member_principal_id
                         left join sys.database_principals dp1 
                           on dp1.principal_id = rm.role_principal_id 
     where u.uid > 4 and u.issqlrole = 0
     --and issqluser = 1 --commented out to include orphaned windows logins
       and l.name is null
    order by 1'
SELECT * FROM #Orphans

需要注意的一点是，每次在新数据库中执行模板时，SQL 模板中的"？"字符都将填充数据库名称。此外，我更改了您的列别名以使用"["字符而不是单引号。

Answer 2

我知道在不同

数据库中运行查询的唯一方法是使用游标循环访问数据库名称并在每次迭代中执行动态查询（具有所需的数据库名称）。

为了摆脱将表变量与动态查询一起使用的困难（至少对我来说），我使用了一个本地临时表（单个#前缀）来存储结果。

为此，我必须在查询中的每个 schema.table 前面加上一个占位符，该占位符在每次迭代中都替换为数据库名称。这样，每个表都将使用 db.schema.table 进行完全限定。

还要注意，使用这样的EXEC会使你容易受到 sql 注入的影响。

我希望这对您有所帮助：

CREATE TABLE #results (
    currdb sysname,
    uid int,
    uname sysname,
    usid int,
    rpid int,
    rname sysname)
DECLARE @sqltemplate VARCHAR(4000), @sql VARCHAR(4000)
SET @sqltemplate = 'SELECT
   ''[@db]'', u.uid, u.name, u.sid, rm.role_principal_id, dp1.name
    FROM [@db].sys.sysusers u
        LEFT JOIN [@db].sys.syslogins l ON UPPER(u.sid) = UPPER(l.sid)
        INNER JOIN [@db].sys.database_role_members rm
            ON rm.member_principal_id = u.uid
        LEFT JOIN [@db].sys.database_principals dp
            ON dp.principal_id = rm.member_principal_id
        LEFT JOIN [@db].sys.database_principals dp1
            ON dp1.principal_id = rm.role_principal_id
    WHERE u.uid > 4 AND u.issqlrole = 0 AND l.name IS NULL
    ORDER BY 1'
DECLARE @dbname sysname
DECLARE dbnames CURSOR FOR
    SELECT name FROM sys.databases WHERE database_id > 4
OPEN dbnames
FETCH NEXT FROM dbnames INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
  SET @sql = REPLACE(@sqltemplate, '@db', @dbname)
  INSERT #results
    EXEC (@sql)
  FETCH NEXT FROM dbnames INTO @dbname
END
CLOSE dbnames
DEALLOCATE dbnames
SELECT * FROM #results
DROP TABLE #results