В SQL Server 2005, как я могу написать запрос, чтобы перечислить все логин, их роль сервера, соответствие пользователя во всех ролях db, db?

Question

Я не совсем уверен насчет представлений каталога, связанных с безопасностью в SQL Server 2005 или 2008. Я хочу перечислить все имена входа, их роли сервера, их соответствующих пользователей во всей базе данных, все роли базы данных в одном запросе. Как я могу написать запрос?

Я знаю, что есть некоторые представления каталога, но я не знаком с их отношением. К этим представлениям каталога относятся: sys.database_role_member, sys.database_principals, sys.server_role_member, sys.server_principals.

Благодарю.

Answer 1

Вы не можете иметь один список запросов для всех баз данных, потому что список динамический. Лучше всего использовать 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;

Вы можете расширить его, включив в него роли серверов и членство в ролях базы данных, как только вы определите правильную форму набора результатов, чтобы объединить всю эту информацию в одной таблице.

Answer 2

Вот запрос, который перечислит все имена входа с назначенными им ролями уровня сервера.

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