What's the difference between Single User and Restricted User with MSSQL

Single_User

  1. Restricts access to a database to only one user at a time.
  2. It can be ANY USER.
  3. First come, first served. This does not restrict access to the dba.
ALTER DATABASE sql911
SET single_user WITH ROLLBACK IMMEDIATE;
go
Command(s) completed successfully.

Then you can do your maintenance, database restore,....

Don't forget to return the database to multiuser mode for regular use.

ALTER DATABASE sql911
SET multi_user;
go

Restricted_User

  1. Restricts access to a database to only members of:
    1. db_owner
    2. dbcreator
    3. sysadmin
  2. Often used for data import,...
  3. This means that multiple users can still be logged into the database, as long as they are dbo,...
ALTER DATABASE sql911
SET restricted_user WITH ROLLBACK IMMEDIATE;
go
Command(s) completed successfully.

Don't forget to return the database to multiuser mode for regular use.

ALTER DATABASE sql911
SET multi_user;
go

Multi_User

  1. Place the database back into multi-user mode for regular use, so everybody can use the database again.