kick users in query analyzer - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

kick users in query analyzer

Author

Topic

SiliconSeed
Starting Member

3 Posts

Posted - 2008-07-29 : 12:25:07

Hello,

I am trying to figure out how to drop all the users from a database so I can actualy do some work on it.

Everywhere I read I get this line of code:

ALTER DATABASE @DATABASE
SET SINGLE_USER

This causes an endless loop in SQL2005 if users are in the database.

I also found this:
SELECT spid FROM master..sysprocesses WHERE dbid = DB_ID(@DATABASE)
AND spid != @@SPID
EXEC('KILL '+RTRIM(@spid) )

This gives a syntax error near RTRIM.

contrari4n
Starting Member

27 Posts

Posted - 2008-07-29 : 19:53:11

ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Make sure the users are aware you are planning to do this first or you won't be very popular!!

Richard Fryar
http://www.sql-server-pro.com
SQL Server Articles and Tips

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources