I'm wondering if this is the best way to set a database to single_user, do an update or schema change and then set back to multi_user. Should transactions be incorporated? Nobody likes "go to" these day, but it works here. I've confirmed that it kicks out web services users as well as others in the sysadmin group.
use master
go
DECLARE @intErrorCode INT
alter database inventory set SINGLE_USER with ROLLBACK immediate
SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO PROBLEM
-- do database update, drop databae, schema change etc
print ' doing database work '
SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO PROBLEM
alter database inventory set MULTI_USER with ROLLBACK immediate
SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO PROBLEM
PROBLEM:
IF (@intErrorCode <> 0) BEGIN
PRINT 'Unexpected error occurred!'
print @@error
END
-- check to see if was set back to multi_user
exec master.dbo.sp_dboption 'inventory'