sql-server – 列出给定登录的所有映射用户的查询

发布时间：2020-12-12 05:54:41 所属栏目：MsSql教程来源：网络整理

导读：查看特定登录的属性时,可以看到映射到该登录的用户列表：我分析了SQL Server Management Studio(SSMS),我看到SSMS一次连接到每个数据库,并从sys.database_permissions中检索信息是否可以编写单个查询来检索上面显示的用户映射信息,或者我是否被迫使用游标或

查看特定登录的属性时,可以看到映射到该登录的用户列表：

我分析了SQL Server Management Studio(SSMS),我看到SSMS一次连接到每个数据库,并从sys.database_permissions中检索信息

是否可以编写单个查询来检索上面显示的用户映射信息,或者我是否被迫使用游标或sp_MSforeachdb或类似的东西？

解决方法

这是使用动态SQL的一种方法.没有迭代,没有任何方法可以做到这一点,但这种方法比 undocumented,unsupported and buggy options like sp_MSforeachdb更安全.

这将获得所有在线数据库的列表,映射的用户(如果存在)以及默认模式名称和它们所属的角色的逗号分隔列表.

DECLARE @name SYSNAME = N'your login name'; -- input param,presumably

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'UNION ALL SELECT N''' + REPLACE(name,'''','''''') + ''',p.name,p.default_schema_name,STUFF((SELECT N'','' + r.name 
  FROM ' + QUOTENAME(name) + N'.sys.database_principals AS r
  INNER JOIN ' + QUOTENAME(name) + N'.sys.database_role_members AS rm
   ON r.principal_id = rm.role_principal_id
  WHERE rm.member_principal_id = p.principal_id
  FOR XML PATH,TYPE).value(N''.[1]'',''nvarchar(max)''),1,N'''')
 FROM sys.server_principals AS sp
 LEFT OUTER JOIN ' + QUOTENAME(name) + '.sys.database_principals AS p
 ON sp.sid = p.sid
 WHERE sp.name = @name '
FROM sys.databases WHERE [state] = 0;

SET @sql = STUFF(@sql,9,N'');

PRINT @sql;
EXEC master.sys.sp_executesql @sql,N'@name SYSNAME',@name;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!