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 .
Technorati Tags: t-sql