在 SQL Server 2005 中,如何编写查询以列出所有登录名、它们的服务器角色、所有数据库中对应的用户、数据库角色? [英] In SQL Server 2005, how can I write a query to list all login, their server role, correspond user in all db, db role?
问题描述
我不清楚 SQL Server 2005 或 2008 中与安全相关的目录视图.我想在一个查询中列出所有登录名、它们的服务器角色、它们在所有数据库中的对应用户、所有数据库角色.我该如何编写查询?
I'm not clear about the security-related catalog views in SQL Server 2005 or 2008. I want to list all logins, their server roles, their correspond users in all database, all database roles in one query. How can I write the query?
我知道有一些目录视图可以使用,但我不熟悉它们的关系.这些目录视图包括:sys.database_role_member、sys.database_principals、sys.server_role_member、sys.server_principals.
I know there are some catalog views to use, but I'm not familiar with their relation. These catalog views include: sys.database_role_member, sys.database_principals, sys.server_role_member, sys.server_principals.
谢谢.
推荐答案
您不能用一个查询列出所有数据库,因为该列表是动态的.您最好的选择是使用 sp_msforeachdb
并批量构造结果并返回:
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.
这篇关于在 SQL Server 2005 中,如何编写查询以列出所有登录名、它们的服务器角色、所有数据库中对应的用户、数据库角色?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!