Post reply

Single user

  • November 9, 2011 at 6:23 am

    #250534

    Hi,

    I have executed ALTER DATABASE <MYDBNAME> SET single_user..then i have tried to see the database properties but i couldn't able to see...

    To my knowledge when we set single user only one user able to access to that database.But that user must have administrative privileges.....(sa is my database owner)..

    Thanks

  • November 9, 2011 at 6:36 am

    #1405114

    are you using a connection in query window? if yes close that and then try.

    you can also query sys.sysprocesses to check who is connected to that database.

  • November 9, 2011 at 6:56 am

    #1405129

    when i try to execute below one

    SELECT DB_NAME(dbid) as DBName, COUNT(dbid) as NumberOfConnections,loginame as LoginName FROM sys.sysprocesses WHERE dbid > 0 GROUP BY dbid, loginame

    which gives no.of user connections for sysdatabases..

    could u give me query for find out the no . of connections for user defined databases

  • November 9, 2011 at 7:13 am

    #1405148

    select * from sys.sysprocesses where dbid=db_id('my_db')

  • November 10, 2011 at 3:19 am

    #1405597

    rocky@123 (11/9/2011)

    To my knowledge when we set single user only one user able to access to that database.But that user must have administrative privileges.....(sa is my database owner)..

    If you simply set the database in single user mode, non-admin user can still connect to the database as long as there are no other connections. If you want only administrators being able to connect you should use ALTER DATABASE myDB SET RESTRICTED_USER WITH ROLLBACK

    [font="Verdana"]Markus Bohse[/font]

  • November 11, 2011 at 12:37 am

    #1406095

    Hi Markus..agree with you...But you know what i mean , if you take some scenarios like ..

    My database is crash unfortunately and i have .bak file for that database..So now i want to restore the database using that backup..I don't want to allow the other users untilrestoration completed..

    So at that time i will put my database to single user access..And for restoring the database(like some maintenance activities ) user must have some priveliges either that person should be sa or dbowner or dbcreator etc...am i correct?

  • November 11, 2011 at 12:58 am

    #1406100

    If your database is already f...d, then you can simply create the new database with the backup/s you have.

    If you database is alive and need to be refreshed with backup/s then you can revoke all the user's permission in database by first scripting out the existing permission. This will help you to restore without pain as none of the user have access to the database.

    ----------
    Ashish

  • November 11, 2011 at 1:01 am

    #1406102

    Like I wrote before in single_user mode any use can still connect as long as there are no other connections. Only when you use restricted user mode access is limited to sysadmins and db_owners.

    Also if you don't use ROLLBACK all existing connections can go on. Only new connections will be blocked.

    [font="Verdana"]Markus Bohse[/font]

Viewing 8 posts - 1 through 7 (of 7 total)

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