如何查询数据库的管理角色,
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.
分享到:
相关推荐
默认情况下域内的认证的用户(默认情况下就是域内的成员)都有权限将一台客户端加入到域的,但是默认情况下普通的user只有10次机会将客户端计算机加入到域,一个帐户超过10次再尝试将客户端计算机加入域时,就会报错(是...
②【是,把此计算机作为下面域的成员】。此时必须在下方的【工作组或计算机域】文本框中 输入域的名称,在加入域时系 统会要求用户输入域用户帐户 与密码。在安装系统时一般选 择不要加入域的方式,单击 【下一步】...
3-12受限制的组 控制计算机本地组成员11:56 3-13组策略控制服务启动类型和权限08:21 3-14集中管理域中计算机注册表安全06:48 3-15设置域中计算机文件夹NTFS权限02:05 3-16使用组策略管理计算机防火墙07:22 3-17使用...
组织单元是可以指派组策略设置或委派管理权限的最小作用域或单位。使用组织单元,用户可在组织单元中代表逻辑层次结构的域中创建容器。这样用户就可以根据用户的组织模型管理账户和资源的配置和使用。 1 e+ }# `! n6...
Windows 2000 安全配置 本模块内容 目标 ...内置组 ...当审核日志满时生成管理...• 域成员便携式计算机 • 独立工作站 • Microsoft Windows 2000 Server™ 操作系统 • 域控制器 • 域成员服务器 • 独立服务器
来宾组,来宾组跟普通组Users的成员有同等访问权,但来宾账户的限制更多。 5.Everyone 所有的用户,这个计算机上的所有用户都属于这个组。 6.SYSTEM组 这个组拥有和Administrators一样甚至更高的权限,在...
服务器角色添加 AD域服务控制器 文件和存储管理 域控建立完成,每个员工一个账户 建立公司文件夹,文件夹下建立成员文件夹 目标: 共享文件夹设置,实现SMB访问,每个人只能看到自己有权限的文件夹,其他没有权限的...
废话不多说了,直接给大家贴代码了,具体代码如下所示: --服务器角色: --固定服务器角色具有一组固定的权限,并且适用于整个服务器范围。...-- 为需要执行大容量插入到数据库的域账户而设计。 --2、Db
(图7) 第六步:打开"管理工具",发现这台主机还是一个域控制器,打开"域用户和计算机 ",域成员竟然有43511个如图8,看来这个企业的局域网的规模不小。(图8) 总结:一年前的漏洞到现在还没有打,管理员太失职了。 ...
//使用SQL Server用户账户连接到分发服务器//将所有的计算机置于一个Microsoft Windows域,或者在所有计算机上设置一个具有相同密码的公共用户。然后在分发服务器和订阅服务器之间使用Windows信任连接 57、 为什么...