在SQL Server 2005中，如何编写查询来列出所有登录名、它们的服务器角色、所有数据库中对应的用户、数据库角色？

发布于 2024-09-14 06:04:37 字数 267 浏览 1 评论 0原文

我不清楚SQL Server 2005或2008中与安全相关的目录视图。我想在一个查询中列出所有登录名、它们的服务器角色、它们在所有数据库中的对应用户、所有数据库角色。我该如何编写查询？

我知道有一些目录视图可以使用，但我不熟悉它们的关系。这些目录视图包括：sys.database_role_member、sys.database_principals、sys.server_role_member、sys.server_principals。

谢谢。

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

随梦而飞# 2024-09-21 06:04:37

您无法通过一个查询列出所有数据库，因为该列表是动态的。最好的选择是使用 sp_msforeachdb 并批量构造结果并返回它：

set nocount on;
create table  #result (sid varbinary(85), 
 server_principal_id int,
 database_id int,
 database_principal_id int);

exec ms_foreachdb 'insert into #result 
  (server_principal_id, database_id, database_principal_id)
select s.principal_id, 
  db_id(''?''),
  d.principal_id
from sys.server_principals s
join [?].sys.database_principals d
  on s.sid = d.sid;';

select * from #result;

一旦找到正确的结果集，您就可以扩展它以包括服务器角色和数据库角色成员资格形状将所有信息聚合在一个表中。

You cannot have one query list all databases because the list is dynamic. Your best bet is to use sp_msforeachdb and have a batch construct the result and return it:

set nocount on;
create table  #result (sid varbinary(85), 
 server_principal_id int,
 database_id int,
 database_principal_id int);

exec ms_foreachdb 'insert into #result 
  (server_principal_id, database_id, database_principal_id)
select s.principal_id, 
  db_id(''?''),
  d.principal_id
from sys.server_principals s
join [?].sys.database_principals d
  on s.sid = d.sid;';

select * from #result;

You can extend this to include the server roles and database roles memberships once you figure out a proper result set shape to aggregate all that information in a single table.

回复收藏 0 原文

夕嗳→ 2024-09-21 06:04:37

以下查询将列出所有登录名及其分配的服务器级角色。

select 
  login_name = pa.name, 
  --pa.principal_id, m.member_principal_id, m.role_principal_id,pb.principal_id,
  role_name = pb.name
from
  sys.server_principals pa
  inner join
  sys.server_role_members m on pa.principal_id = m.member_principal_id
  inner join
  sys.server_principals pb on m.role_principal_id = pb.principal_id
order by
  pa.name,
  pa.principal_id

Here is a query that will list all logins with their assigned server-level roles.

select 
  login_name = pa.name, 
  --pa.principal_id, m.member_principal_id, m.role_principal_id,pb.principal_id,
  role_name = pb.name
from
  sys.server_principals pa
  inner join
  sys.server_role_members m on pa.principal_id = m.member_principal_id
  inner join
  sys.server_principals pb on m.role_principal_id = pb.principal_id
order by
  pa.name,
  pa.principal_id

回复收藏 0 原文

~没有更多了~