暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

batch change read only DB in mssql 2K

原创 Anbob 2013-01-22
509
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'
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论