THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Piotr Rodak

if datepart(dw, getdate()) in (6, 7)
    use pubs;

database in use–a little trick to disconnect users

While I am still waiting for my SQL Server 2008 R2 Dev Edition to come, there’s a little trick you can do if you want to drop users from your database and you don’t have sysadmin rights to kill sessions.

You can set database to single user mode with this command:

1 alter database your_database set single_user with rollback immediate

This command disconnects all connected users and rolls back any running transactions. It doesn’t allow you to disconnect particular users, but it may be handy if you are db_owner of the database but don’t have sa right to run KILL command.

I use this command to restore database from snapshot – the restore command requires that there are no active connection to database while it is executed.


That’s about it for today, next article will be longer, I promise Smile.


Technorati Tags: ,
Published Monday, November 22, 2010 7:17 PM by Piotr Rodak
Filed under: ,


No Comments
New Comments to this post are disabled
Privacy Statement