shadow_user777 1.Логон должен быть по любому. Иначе как сервер знает кому отсылaть данные? И кому записывать 1.
2.
Цитата: При коннекте в таблице статуса коннекта 0 сменяется на 1
Cтатус сменяется для конкретного логина? Тогда попробовать что-нибуть с instead тригером.
Forcefully Disconnect All Users and Drop a Database [more=kill all processes on the server]create procedure spKillUsers
@dbName varchar(32)
as
declare @spid smallint
declare @cmd varchar(32)
create table #tmp
(spid smallint,
status varchar(32),
loginame varchar(32),
hostname varchar(32),
blk char(8),
dbname varchar(32),
cmd varchar(255))
declare cLogin cursor for select spid from #tmp where dbname = @dbName
insert into #tmp exec sp_who
open cLogin
fetch cLogin into @spid
while @@fetch_status = 0
begin
select @cmd = 'kill ' + CONVERT(char, @spid)
print @cmd
execute( @cmd )
fetch cLogin into @spid
end
close cLogin
deallocate cLogin
go
GRANT EXECUTE ON dbo.spKillUsers TO public
go
[/more]
[more=Procedure to forcibly disconnect users from a database]
http://www.wisesoft.co.uk/Articles/SQL%20Server/sp_killusers.doc Procedure to forcibly disconnect users from a database
About
This stored procedure will take the name of a database and disconnect all the users from the database. Any pending transactions will be rolled back. Please use this function with caution.
The procedure creates a temporary table and stores the results of the sp_who system function in the temporary table. A cursor is created to select the SPID column from the temporary table where the dbname column is equal to the @database parameter submitted to the function. Dynamic SQL is generated to run the KILL statement for each SPID connected to the specified database. A check is made to ensure that the KILL statement is not run for the current user’s connection.
T-SQL Code (SQL 2000)
create procedure sp_killusers(@database varchar(30))
as
----------------------------------------------------
-- * Created By David Wiseman, Updated 03/11/2006
-- * Version for SQL 2000
-- *
http://www.wisesoft.co.uk -- * This procedure takes the name of a database as input
-- * and uses the kill statment to disconnect them from
-- * the database.
-- * PLEASE USE WITH CAUTION!!
-- * Usage:
-- * exec sp_killusers 'databasename'
----------------------------------------------------
set nocount on
-- Create temp table to store results of sp_who
create table #who
(
spid int,
ecid int,
status varchar(30),
loginname varchar(35),
hostname varchar(15),
blk int,
dbname varchar(30),
cmd varchar(30)
)
declare @spid int
declare @killstatement nvarchar(10)
-- Run sp_who to get the users connected to each database
insert into #who exec sp_who
-- Declare a cursor to select the users connected to the specified database
declare c1 cursor for select spid from #who where dbname = @database
open c1
fetch next from c1 into @spid
-- for each spid...
while @@FETCH_STATUS = 0
begin
-- Don't kill the connection of the user executing this statement
IF @@SPID <> @spid
begin
-- Construct dynamic sql to kill spid
set @killstatement = 'KILL ' + cast(@spid as varchar(3))
exec sp_executesql @killstatement
-- Print killed spid
print @spid
end
fetch next from c1 into @spid
end
-- Clean up
close c1
deallocate c1
drop table #who
T-SQL Code (SQL 2005)
create procedure [dbo].[sp_killusers](@database varchar(30))
as
----------------------------------------------------
-- * Created By David Wiseman, Updated 19/11/2006
-- * Version for SQL 2005
-- *
http://www.wisesoft.co.uk -- * This procedure takes the name of a database as input
-- * and uses the kill statment to disconnect them from
-- * the database.
-- * PLEASE USE WITH CAUTION!!
-- * Usage:
-- * exec sp_killusers 'databasename'
----------------------------------------------------
set nocount on
declare @spid int
declare @killstatement nvarchar(10)
-- Declare a cursor to select the users connected to the specified database
declare c1 cursor for select request_session_id
from sys.dm_tran_locks
where resource_type='DATABASE'
AND DB_NAME(resource_database_id) = @database
open c1
fetch next from c1 into @spid
-- for each spid...
while @@FETCH_STATUS = 0
begin
-- Don't kill the connection of the user executing this statement
IF @@SPID <> @spid
begin
-- Construct dynamic sql to kill spid
set @killstatement = 'KILL ' + cast(@spid as varchar(3))
exec sp_executesql @killstatement
-- Print killed spid
print @spid
end
fetch next from c1 into @spid
end
-- Clean up
close c1
deallocate c1
[/more]
Почитайте BOL, что ли. Или хорошую книжку по SQL... 
