Killing User Connection(session) connected to the Database.

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Sourav Mukherjee » Killing User Connection(session) connected to...

Killing User Connection(session) connected to the Database.

Rate Topic

Display Mode

Topic Options

Author

Message

SQL-DBA-01

Posted Friday, April 08, 2011 12:17 AM

Right there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 9:12 AM
Points: 738, Visits: 1,125

Comments posted to this topic are about the item Killing User Connection(session) connected to the Database.

Thanks.

Post #1090417

SQL@DOS

Posted Friday, April 08, 2011 7:10 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 17, 2012 8:32 AM
Points: 114, Visits: 72

If your application or access to the database doesn't use dbo or sa authority you can just use:

ALTER DATABASE db_name SET RESTRICTED_USER With ROLLBACK IMMEDIATE

This will drop all user connections and rollback any non-committed transactions. When RESTRICTED_USER is specified, only members of the db_owner, dbcreator, or sysadmin roles can use the database.

We use this to restrict access to a system so we can backup Production databases prior to a deployment. Smile

Post #1090593

Colin Millerchip

Posted Monday, April 18, 2011 10:45 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 9:43 AM
Points: 8, Visits: 245

Hi Sourav,

You've marked this as being compatible with SQL Scripts Manager (http://www.red-gate.com/products/dba/sql-scripts-manager/), but I don't think it is. Are you able to edit it to correct this?

Thanks,

Colin.

Post #1095150

SQL-DBA-01

Posted Monday, April 18, 2011 2:09 PM

Right there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 9:12 AM
Points: 738, Visits: 1,125

Sorry, couldn't get the hyperlink. Could you please suggest where you can see the interlink?

Thanks.

Post #1095289

Colin Millerchip

Posted Monday, April 18, 2011 4:44 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 9:43 AM
Points: 8, Visits: 245

Sorry, I wasn't clear in my previous post. When you created your post at http://www.sqlservercentral.com/Contributions/New/Script, I believe you ticked the box to "Include code for SQL Scripts Manager", and then you pasted your T-SQL script into the box that's supposed to contain the XML-wrapped script that runs within the SQL Scripts Manager tool; whereas you needed to paste your code into the box entitled "Insert your SQL code here".

So your script is now showing up at http://www.sqlservercentral.com/Scripts/rgtool/, which is a list of scripts that have been wrapped up in the XML format that allows them to run within SQL Scripts Manager.

Are you able to edit your contribution, to remove the SQL Scripts Manager content?

Best regards,

Colin.

Post #1095359

« Prev Topic | Next Topic »

Permissions