Database Single user access

  • For the past two days, iam running out to problem in setting Databse to single user access. Also, i got the solutions which is SINGLE USER MODE. But, friends is there any other option to change db to Single user access.

    Plz Help.

    Thanks in Advance.

  • Single user mode will take the first connection that comes along.

    We usually try to set servers to restricted user mode. That way, only people with admin privileges can connect.

    You can issue the command with rollback immediate to kick all your users out, but it will rollback open transactions, possibly losing application data in apps that don't deal with rollbacks appropriately.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
    The Scary DBA
    Author of: SQL Server 2022 Query Performance Tuning, 6th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • i don't know which problem you are running into when trying to set the DB in single user mode... but usually this would work

    ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    and to let people back on...

    ALTER DATABASE dbname SET MULTI_USER

    you can also use sp_dboption as shown below, but the proc is deprecated since 2000 or 2005... can't recall

    sp_dboption 'dbname', 'single user', 'true'

    and then...

    sp_dboption 'dbname', 'single user', 'false'

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply