查询管理权限的账户，域组，成员

如何查询数据库的管理角色，

set xact_abort off;
set nocount on;

declare @principals table
(
 primary key ( principal_type, principal_name, member_name ),
 principal_type varchar(180) not null,
 principal_name varchar(180) not null,
 member_name varchar(180) not null,
 create_date datetime null,
 modify_date datetime null,
 admin_role_desc varchar(180) null,
 logininfo_note varchar(8000) null
);

-- insert all accounts and groups into result:
insert into @principals
select
 type_desc,
 name,
 '-' as member_name,
 create_date,
 modify_date,
 (
  case is_srvrolemember('sysadmin',name) when 1 then 'sysadmin|' else null end
  + case is_srvrolemember('securityadmin',name) when 1 then 'securityadmin|' else null end
  + case is_srvrolemember('serveradmin',name) when 1 then 'serveradmin|' else null end
  + case is_srvrolemember('setupadmin',name) when 1 then 'setupadmin|' else null end  
  + case is_srvrolemember('processadmin',name) when 1 then 'processadmin|' else null end  
  + case is_srvrolemember('diskadmin',name) when 1 then 'diskadmin|' else null end  
  + case is_srvrolemember('dbcreator',name) when 1 then 'dbcreator|' else null end  
  + case is_srvrolemember('bulkadmin',name) when 1 then 'bulkadmin|' else null end
 ) as admin_role_desc,
 null as logininfo_note
from sys.server_principals
;

declare @admin_groups table
(
 primary key ( group_type, group_name ),
 group_type varchar(180) not null,
 group_name varchar(180) not null
);

declare @logininfo table
(
 primary key ( account_name, permission_path ),
 account_name varchar(180) not null,
 type varchar(180) null,
 privilege varchar(180) null,
 mapped_login_name varchar(180) null,
 permission_path varchar(180) not null
);

-- For each domain group with admin privilages,
-- insert one record for each of it's member accounts into the result:
declare @group_type varchar(180), @group_name varchar(180);
select @group_type = '*', @group_name = '*';
while @group_name is not null
begin
 select @group_type = null, @group_name = null;
 select top 1 @group_type = principal_type, @group_name = principal_name
  from @principals
   where principal_type in ('windows_group')
    and member_name = '-'      
    and admin_role_desc is not null
    and principal_name not in (select group_name from @admin_groups);
 if @group_name is not null
 begin
  -- Call xp_logininfo to return all domain accounts belonging to group:
  insert @admin_groups values (@group_type, @group_name);
  begin try
   delete from @logininfo;
   insert into @logininfo
    exec master..xp_logininfo @group_name,'members';
   -- Update number of members for group to logininfo_note:
     update @principals
    set logininfo_note = 'xp_logininfo returned '+cast(@@rowcount as varchar(9))+' members.'
     where principal_type in ('windows_group')
      and principal_name = @group_name
      and member_name = '-';   
  end try
  begin catch
   -- If an error occurred, then update it to logininfo_note, and then continue:
     update @principals
    set logininfo_note = 'xp_logininfo returned error '+cast(error_number() as varchar(9))
     where principal_type in ('windows_group')
      and principal_name = @group_name
      and member_name = '-';
  end catch
  -- For each group member, insert a record into the result:
  insert into @principals
  select
   @group_type as principal_type,
   @group_name as principal_name,
   account_name as member_name,
   null as create_date,
   null as modify_date,
   (select admin_role_desc
    from @principals
     where principal_type = @group_type
     and principal_name = @group_name
     and member_name = '-') as admin_role_desc,
   null as logininfo_note
  from @logininfo;
  -- For each group member that is a group,
  -- insert a record of type 'WINDOWS_GROUP' into the result:
  insert into @principals
  select
   'WINDOWS_GROUP' as principal_type,
   account_name as principal_name,
   '-' as member_name,
   null as create_date,
   null as modify_date,
   (select admin_role_desc
    from @principals
     where principal_type = @group_type
     and principal_name = @group_name
     and member_name = '-') as admin_role_desc,
   null as logininfo_note
  from @logininfo
   where type = 'group'
   and not exists
    (select 1
     from @principals
      where principal_type = 'WINDOWS_GROUP' and principal_name = account_name and member_name = '-'
    );
 end; 
end;

-- Return result of only those accounts, groups, and members who have an admin role:
select principal_type, principal_name, logininfo_note, member_name, create_date, modify_date, admin_role_desc
 from @principals
  where admin_role_desc is not null
   order by principal_type, principal_name, member_name;

 

要注意的是，数据库有些角色，并不具有完全管理权限，如：

db_datareader;

view definition;

showplan;

view server state.