March 23, 2009 at 2:31 am
Hi,
i want to drop about 400 user from a database role. I know it works with sp_droprolemember. But how can i do this for all user are member of the role, except for 2.
Thank´s
March 23, 2009 at 3:14 pm
I think I'd drop all the role members then re-add the two logins to the role. Here's a way to script the sp_droprolemember statements:
set nocount on
create table #rolemembers
(Group_name varchar(30),
Group_id int,
Users_in_group varchar(30),
Userid int)
insert into #rolemembers
exec sp_helpuser 'db_datawriter'
select 'sp_droprolemember ' + '''' + Group_name + '''' + ',' + '''' + Users_in_group + '''' from #rolemembers
Greg
March 24, 2009 at 1:59 am
Hi, Thank´s a lot. I just wrote the following script, and it work´s fine
Use [Test]
declare
@username varchar(64)
declare c1 cursor for select name from sysusers
where uid > 1
and uid < 16384 -- ab 16384 db_owner etc.
and [name] not in ('Domain\Service_SQL','Domain\Service_Cluster','Domain\SQL-Cluster-Group') -- Ausnahmen zur Sicherheit
and (name like '%xxx%' or name like '%yyyyyy%')
open c1
fetch next from c1 into @username
While @@fetch_status <> -1
begin
print @username
EXEC sp_droprolemember 'db_owner', @username
--EXEC sp_addrolemember 'db_owner', @username -- wieder hinzufügen
fetch next from c1 into @username
end
close c1
deallocate c1
GO
-- Prüfung wer noch über ist:
--sp_helprolemember 'db_owner'
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply