Disconnect a user

• Daniela-267581

  Ten Centuries

  Points: 1119

  More actions

  May 24, 2006 at 6:51 am

  #114254

  Hi,

  I need from c# to drop an sql server login.

  I create from code that login, work with him, and finally I have to drop it.

  Now, the problem is when I send to sql server "drop login[Test]" it says that cannot drop the login, because he is still connected.

  How can I send to the server an sql coomnad to disconnect the login first? the t-sql command I mean

  Thanks

  ---

  Daniela

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  May 24, 2006 at 8:32 am

  #639795

  Daniela

  You can use the following T-SQL to generate the T-SQL to disconnect the logins.  Or, if you want to do it all in one batch, you can use a cursor.

  John

  create table #who (

   spid smallint,

   ecid smallint,

   status varchar(50),

   loginame varchar(100),

   hostname varchar(250) null,

   blk smallint,

   dbname sysname null,

   cmd varchar(250)

  &nbsp

  set nocount on

  insert into #who (spid, ecid, status, loginame, hostname, blk, dbname, cmd)

   exec sp_who

  declare @deaduser varchar(100)

  set @deaduser = 'login_to_be_removed'

  select 'KILL ' + cast (spid as varchar(8))

   from #who

   where loginame = @deaduser

  select 'exec sp_drop_login ''' + @deaduser + ''''

  set nocount off