代码之家 › 专栏 › 技术社区 › EKS

更改多个安全帐户的角色

sql-server

EKS · 技术社区 · 14 年前

我在sql数据库上有很多安全帐户,我想基于一个简单的字符串比较来删除/添加角色。

基本上我想列出所有账户
使用“MyDomain”
添加角色B。

我现在发现的是 sp_helprolemember 列出所有账户 sp_addrolemember sp_droprolemember . 我的问题是我不知道如何从和它一起工作。

DROP TABLE [dbo].[XTemp]
create table XTemp(DbRole sysname,MemberName sysname,MemberSID varbinary(85) )

insert XTemp exec sp_helprolemember 
select * from XTemp

我制作了一个永久表,使测试和调试更简单。

SELECT [DbRole]
      ,[MemberName]
      ,[MemberSID]
  FROM [ARTICLE].[dbo].[XTemp]
  WHERE MemberName like Domain\%'
    exec sp_addrolemember 'OldRole MemberName

2 回复 | 直到 14 年前

Ed Harper 14 年前

假设您使用的是SQL 2005或更高版本,并执行 sp_helprolemember 如果没有参数,这是一个运行(使用 sp_helptext ):

  select DbRole = g.name, MemberName = u.name, MemberSID = u.sid  
   from sys.database_principals u, sys.database_principals g, sys.database_role_members m  
   where   g.principal_id = m.role_principal_id  
    and u.principal_id = m.member_principal_id  
   order by 1, 2

这将使您能够将所需的信息收集到临时表中。

如果您希望坚持记录的行为,可以将SP的输出存储到临时表中:

create table #t
(DbRole sysname,
MemberName sysname,
MemberSID varbinary(85) 
)

insert #t
exec sp_helprolemember 

select * from #t

有两种方法可以使用这些数据来修改您的系统。一种是使用光标:

DECLARE @memberName sysname

DECLARE curMember CURSOR fast_forward FOR
SELECT  MemberName
FROM #t 
WHERE MemberName LIKE 'Domain\%' 

OPEN curMember

FETCH NEXT FROM curMember INTO @memberName

WHILE @@FETCH_STATUS = 0
BEGIN

    EXEC sp_addrolemember 'OldRole', @memberName
    FETCH NEXT FROM curMember INTO @memberName

END

CLOSE curMember
DEALLOCATE curMember

另一种是使用动态SQL:

DECLARE @sql NVARCHAR(MAX),

SELECT @sql = 'EXEC sp_addrolemember ''OldRole'', ''' + MemberName + ''''
FROM #t
WHERE MemberName LIKE 'Domain\%' 

EXEC sp_executesql @stmt = @sql

正如您所看到的,动态SQL版本更加紧凑,但是需要更多的精力来维护。

记住,在执行任一语句之后,从 帮助角色成员 进入表不再是最新的,可能应该刷新。

Marek Grzenkowicz 14 年前

您可以使用Excel生成SQL查询-我知道这听起来很蹩脚,但它非常简单和强大。它特别适合于必须执行一次或只执行一次的任务。

删除不需要的行和列。
="EXEC sp_dropsrvrolemember '"&A1&"', 'sysadmin'" )为存储在A列中的值生成查询(公式当然可以引用多个包含输入数据的列,并生成真正复杂的查询)。
将生成的查询从Excel复制到Management Studio。