ANBOB™

I had an issue while dropping a  mssql user today, I was unable to drop the user and it failed  with the user in an read_only db. I had many db in the server(more than 200),and many read-only db,so To delete an user must setting  there DB to read write status.

1,setting to read write

Declare @Name varchar(100)
declare @stat varchar(10)

--DROP TABLE #readonly_dbs
create table #readonly_dbs(dbname varchar(100))
DROP TABLE #tmp
create table   #tmp  (oname varchar(100),value varchar(10))
Declare Cur Cursor For select NAME from dbo.sysdatabases
Open Cur
Fetch next From Cur Into @Name
While @@fetch_status=0
Begin
insert into #tmp
exec  sp_dboption  @name, 'read only';
select @stat=value  from #tmp
if (@stat='ON')
begin

insert into #readonly_dbs values(@name);
print 'OK'
exec sp_dboption  @name, 'read only','FALSE'
delete from #tmp

end;
Fetch Next From Cur Into @Name
End
Close Cur
Deallocate Cur

2,drop user
using SQL SERVER Enterprise Manager

3,setting to read only

select * from #readonly_dbs
–for example
alter database dbname set READ_ONLY with no_wait
or
exec sp_dboption dbname ‘read only’,’TURE’

打赏

微信扫一扫，打赏作者吧～