急呀!为什么用exec sp_dboption 'FOE','single user','true'报错.

[zfzhou]

错误信息:
Server: Msg 5070, Level 16, State 2, Line 1
Database state cannot be changed while other users are using the database 'FOE'
Server: Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
sp_dboption command failed.

更麻烦的是,我exec sp_dboption 'pubs','single user','true'也会出相同的错误,

[子陌红尘]

先要断开其他用户与数据库之间的连接，然后用予 sysadmin、dbcreator 或 db_owner权限执行。

[netcoder]

--在查询分析器中,创建并调用下面的存储过程来断开用户连接

/*
断开所有用户打开的连接
*/
use master
go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_killspid]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_killspid]
GO

create proc p_killspid
@dbname varchar(200) --要关闭进程的数据库名
as
declare @sql nvarchar(500)
declare @spid nvarchar(20)

declare #tb cursor for
select spid=cast(spid as varchar(20)) from master..sysprocesses where dbid=db_id(@dbname)
open #tb
fetch next from #tb into @spid
while @@fetch_status=0
begin
exec('kill '+@spid)
fetch next from #tb into @spid
end
close #tb
deallocate #tb
go

--用法
exec p_killspid '数据库名'

[LoveSQL]

你可以把数据库先stop ,然后再start，再执行那段sql语句就没问题啦

[zjmym]

重新启动服务

[zfzhou]

是我所有的数据库都有这样的问题呀,不仅仅是FOE,还有PUBS都是这样的

[zjcxc]

--用这样的语句就行了

alter database FOE set single_user with ROLLBACK IMMEDIATE

[zjcxc]

--上面是设置数据库为单用户模式,下面是恢复多用户
alter database FOE set multi_user with ROLLBACK IMMEDIATE