:::: MENU ::::

Posts Categorized / Logins and Users

  • Aug 06 / 2008
  • 4
dbDigger, Logins and Users, SET Options, SSMS tips and tricks, T-SQL Scripts

Set SQL Server database to single user mode or multi user mode

Often we require to set the database in to single user mode. And after specified operation we switch to multi user mode. Here is a script to perform the switch operation.
For changing AdventureWorks database to single user mode

 
ALTER DATABASE adventureworks  
 SET SINGLE_USER  
 GO  

and for again to multi user mode

 
ALTER DATABASE adventureworks  
 SET MULTI_USER  
 GO  

Also you have to consider that if some users are connected to that database then their connections will be dropped without any warning. And you can not set the system databases master, tempDB, msDB to single user mode. For that purpose you have to start your SQL Server in single user mode.
You can also set a database to single user mode through GUI. For SSMS i will go through for steps

  • Right click on database to be set user mode
  • Go to Options in left panel
  • In right panel you will found ‘Restrict Access’ option
  • Choose single user mode
  • Click OK

Set single user mode through SSMS

Same procedure would be repeated to set the database back to multiple user mode

  • Jul 29 / 2008
  • 0
DBA Interview questions, dbDigger, Logins and Users, Security and Permissions

Purpose of the REFERENCES permission

Question: What is the purpose of the REFERENCES permission?

Answer: Allows the owner of another table to use columns in the table to which they’ve been granted that permission as part of a foreign key.

Explanation: Assigning REFERENCES permission allows the owner of another table to use columns in the table to which they’ve been granted that permission as the target of a REFERENCES FOREIGN KEY constraint with his or her table. However, that person won’t be allowed to change the structure of the table they’ve been granted the permission for.

Note: The Question of day is taken from SQLServercentral.com. I want to compile selected QOD.

Pages:1234
Consult us to explore the Databases. Contact us