时间戳

首页 > 解决方案 > SQL Server：列出每个数据库的用户

sql - SQL Server：列出每个数据库的用户

问题描述

我有几个数据库。我想从这里列出 database/security/users 文件夹中的所有用户，User1 和 User2：

在此处输入图像描述

我想出了这个查询：

SELECT name, type_desc
FROM nameOfMyDatabase.sys.database_principals
WHERE name NOT LIKE 'public' 
  AND name NOT LIKE 'dbo'
  AND name NOT LIKE 'guest'
  AND name NOT LIKE 'INFORMATION_SCHEMA'
  AND name NOT LIKE 'sys'
  AND name NOT LIKE 'db%';

它返回我需要的用户，除了那些默认使用数据库的用户。

现在我想要一个查询，它对我拥有的所有数据库执行此操作，而不必知道它们的名称或任何关于它们的信息。

我找到sp_MSforeachdb了，但不建议在线上的几个站点使用。但如果这是我唯一的选择，它是如何工作的？如何在查询的 SQL 语法中引用当前数据库？（假设它像每个循环一样工作？）

EXEC sp_MSforeachdb 'USE ? SELECT name, type_desc
FROM ''?''.sys.database_principals
Where name not like "public" 
and name not like "dbo"
and name not like "guest"
and name not like "INFORMATION_SCHEMA"
and name not like "sys
and name not like "db%"'

（在这段代码中，我使用 ''?'' 作为对当前数据库的引用，但它返回错误）另外我只想在我创建的数据库上运行查询，而不是生成的系统数据库（master、model、msdb、临时数据库）

除了 sp_MSforeachdb 之外，还有其他方法可以完成这项工作吗？

标签： sqlsql-serverforeach

解决方案

尝试以下

EXEC sp_MSforeachdb '
IF ''?'' NOT IN (''master'',''msdb'',''tempdb'', ''model'')
BEGIN
USE [?]
SELECT name, type_desc
FROM [?].sys.database_principals
WHERE name NOT LIKE ''public'' 
  AND name NOT LIKE ''dbo''
  AND name NOT LIKE ''guest''
  AND name NOT LIKE ''INFORMATION_SCHEMA''
  AND name NOT LIKE ''sys''
  AND name NOT LIKE ''db%''
 
END'

sp_MsForEachDb没有记录，所以这就是不推荐的原因。但是，如果您只需要它用于单个查询而不是生产代码，请随意使用它。

?替换为您的数据库名称，您在哪里丢失[并]围绕您的数据库名称。

您还使用双引号而不是 2 个单引号。由于您在字符串中使用单引号，因此您需要使用单引号对其进行转义。

推荐阅读