Gerard's codebase: Finding who is connected to a database and disconnecting them

Gerard's codebase

This is a personal repository of code snippets and information.

Over the years I have generated lots of little sub programs and stored away acres of useful code snippets. The problem is always to locate them.

Even more time wasting is forgetting how to do simple things when you havnt developed in an environment for a few years (or even a few months)

My new years resolution is to start putting them up in a common place as I produce them. (thanks google)

They are handy for me and, with a bit of a clean up and documentation, they might be handy for others if they wander in here.

Gerard 2008

Thursday, October 23, 2008

Finding who is connected to a database and disconnecting them

--list the connections - run against master
select spid, status, loginame, hostname, blocked, db_name(dbid), cmd from master..sysprocesses where db_name(dbid) = 'database name'

--disconnect the user
kill 999 --where 999 is the spid from the above statement

Alternative
--list all users in a database - run against the db you are looking into
sp_who

--disconnect the user
kill vid

Gerard's codebase