June 7, 2012 at 3:46 am
Wanted to drop all the users and roles from the database which is recently restored from other environment.
Will make a generic new login and assign datareader role to that login on all the db's.
Looking for handy ready script to drop all users and roles from all the db's on instance.
Thanks
Khushbu
June 7, 2012 at 7:29 am
removed reply.
June 7, 2012 at 7:39 am
this would be possible, however, it gets complicated if you are doing it on a SQL 2005+ server.
if the user owns a schema, you can't drop it until the schema is dropped. you cannot drop the schema until its ownership is transferred. etc, etc...
the order of operations would need a lot of logic to actually do this automatically.
June 8, 2012 at 12:46 am
I have made script that deletes all the users, roles from a single DB.
The current script itself uses loop to delete users/roles. When i loop for DB name all i get is only 1 user deleted.
Below is one script that deletes users:
(yea, its using cursors but if everything gets working I will be replacing it with other set based approach)
---------
Declare @Name varchar(156), @Type varchar(156), @Default_Schema_Name varchar(156), @Type_Desc varchar(156)
Declare @sql varchar(128), @ServerRole varchar(156)
Declare UsersCursor Cursor
For
Select Name, Type, Default_Schema_Name, Type_Desc from Sys.Database_Principals
Where Type In ('G', 'S', 'U')
And Principal_id > 4 And Principal_id < 16384
Open UsersCursor
Fetch Next From UsersCursor
Into @Name, @Type, @Default_Schema_Name, @Type_Desc
While @@FETCH_STATUS = 0
Begin
If @Type <> 'R' Begin
Set @sql = 'Drop User [' + @Name + ']'
end
Print @sql
Exec (@sql)
Fetch Next From UsersCursor
Into @Name, @Type, @Default_Schema_Name, @Type_Desc
End
Close UsersCursor
DeAllocate UsersCursor
June 8, 2012 at 5:46 am
yep, your script will delete users but only if they do not own any schemas.
your script will fail if it finds 'USER1' and 'USER1' owned a schema. This happens when 'USER1' had access to the DB via a Windows Group and did not use 2 part naming to create objects.
if you do not use Windows groups and all users are defined to use dbo as their default schema, then you will be ok.
June 8, 2012 at 10:38 am
yea, this script works perfect in my env. ...
But the only glitch is to delete users from all db (in all we have 20 db each server and 200 servers!!!) at one go.
I tried using cursor to loop the db name but somehow it doesnt work as expected (see below code, yea there is scope for fine tune.. but for later)
I have also tried having "delete users" as proc1 while proc2 loops through db name and call proc1.
Declare @Name varchar(156), @Type varchar(156), @Default_Schema_Name varchar(156), @Type_Desc varchar(156)
Declare @sql varchar(128), @ServerRole varchar(156) , @var2 varchar(100)
declare @dbname varchar (100), @sql1 varchar(1000)
Declare database_cur cursor LOCAL for
select name from master.sys.databases where name not in ('master','tempdb','msdb','model')
open database_cur
Fetch Next from database_cur into @dbname
While @@FETCH_STATUS = 0
Begin
SET @sql1 = 'USE ' + @dbname
print 'dbname is ' +@dbname
print @sql1
exec (@sql1)
Declare UsersCursor Cursor LOCAL
For
Select Name, Type, Default_Schema_Name, Type_Desc from Sys.Database_Principals
Where Type In ('G', 'S', 'U')
And Principal_id > 4 And Principal_id < 16384 --and DB_NAME() = @var2
print 'in cur2'
Open UsersCursor
Fetch Next From UsersCursor
Into @Name, @Type, @Default_Schema_Name, @Type_Desc
print @name
print @type
While @@FETCH_STATUS = 0
Begin
print 'ion inner cursor'
If @Type <> 'R' Begin
Set @sql = 'Drop User [' + @Name + ']'
end
Print @sql
--Exec (@sql)
Fetch Next From UsersCursor
Into @Name, @Type, @Default_Schema_Name, @Type_Desc
End
Close UsersCursor
DeAllocate UsersCursor
Fetch Next from database_cur into @dbname
END
Close database_cur
Deallocate database_cur
June 8, 2012 at 11:14 am
try something like this to populate a table variable that you can use to create your PRINT command for execution.
declare @table table ([DB_Name] varchar(128) DEFAULT NULL,[Name] varchar(100), [Type] char(1), Default_Schema_Name varchar(128), Type_Desc varchar(128))
declare @DB_name nvarchar(250)
declare db_crsr_DBS cursor for
select name from master.sys.databases where name not in ('master','tempdb','msdb','model')
open db_crsr_DBS
fetch next from db_crsr_DBS into @DB_name
while @@fetch_status = 0
begin
declare @cmd varchar(1000)
set @cmd = 'Select Name, Type, Default_Schema_Name, Type_Desc from '+@DB_name+'.Sys.Database_Principals
Where Type In (''G'', ''S'', ''U'')
And Principal_id > 4 And Principal_id < 16384'
insert into @table ([Name],[Type],Default_Schema_Name, Type_Desc)
exec (@cmd)
update @table
set [DB_Name] = @DB_name
where [DB_Name] is NULL
fetch next from db_crsr_DBS into @DB_name
end
close db_crsr_DBS
deallocate db_crsr_DBS
select * from @table
June 8, 2012 at 11:23 am
here is your select command from the above script;
select 'USE ['+[DB_Name]+']; DROP USER ['+[Name]+'];' from @table
June 8, 2012 at 11:38 am
Thanks.
Was just checking the script and the select gives desired output..
But to delete them again a dynaminc SQL?
June 8, 2012 at 11:57 am
like this. (uncomment the --exec (@deletecmd) )
USE AT YOUR OWN RISK!!!
create table ##table ([DB_Name] varchar(128) DEFAULT NULL,[Name] varchar(100), [Type] char(1), Default_Schema_Name varchar(128), Type_Desc varchar(128))
declare @DB_name nvarchar(250)
declare db_crsr_DBS cursor for
select name from master.sys.databases where name not in ('master','tempdb','msdb','model')
open db_crsr_DBS
fetch next from db_crsr_DBS into @DB_name
while @@fetch_status = 0
begin
declare @cmd varchar(1000)
set @cmd = 'Select Name, Type, Default_Schema_Name, Type_Desc from '+@DB_name+'.Sys.Database_Principals
Where Type In (''G'', ''S'', ''U'')
And Principal_id > 4 And Principal_id < 16384'
insert into ##table ([Name],[Type],Default_Schema_Name, Type_Desc)
exec (@cmd)
update ##table
set [DB_Name] = @DB_name
where [DB_Name] is NULL
fetch next from db_crsr_DBS into @DB_name
end
close db_crsr_DBS
deallocate db_crsr_DBS
declare @deletecmd varchar(500), @datab_name nvarchar(128), @nameu varchar(128)
declare db_crsr_DB cursor for
select [DB_NAME] , [Name] from ##table
open db_crsr_DB
fetch next from db_crsr_DB into @datab_name, @nameu
while @@fetch_status = 0
begin
set @deletecmd = 'USE ['+@datab_name+']; DROP USER ['+@nameu+'];'
print @deletecmd
--exec (@deletecmd)
fetch next from db_crsr_DB into @datab_name, @nameu
end
close db_crsr_DB
deallocate db_crsr_DB
drop table ##table
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply