CODEWITHSUNDEEP
sqlsql-serverdatabasedatabase-administrationadministrator
SqlLearner
SqlLearner

Reputation: 793

How to change database from Single user mode to multi user

I have a Database,Which is in Single user mode ,If i want to access a tables in the database i will be changing in the properties from single to multiuser.How can i make the Database multi user permenantly?.

Upvotes: 7

Views: 51139

Answers (3)

Dũng IT
Dũng IT

Reputation: 2999

Other solution.

I have the same error as you, I have tried all the ways but it doesn't work. Manually below I have restored to the original state of the database.

Step 1: Stop Sql Server Services
Step 2: Cut Data and Log file to other folder.
Step 3: Start Sql Server Services again.
Step 4: In SQL Studio Management -> Delete error database.
Step 5: Stop Sql Server Services and Copy Data to old folder.
Step 6: Attach again and auto change from single user to multi user

This solution test on Sql Server 2017 by me.

Upvotes: 0

SQLChao
SQLChao

Reputation: 7837

If you want to do it in SSMS object explorer.

Right click on your database. Go to properties > options. Scroll to the bottom and find "Restrict Access" and change it to multi_user. Click ok.

Just an alternative to query window. Both do the same the same.

Upvotes: 2

Kris Gruttemeyer
Kris Gruttemeyer

Reputation: 872

ALTER DATABASE [MyDB] SET MULTI_USER

If it throws an error like user is already connected to it, select 'master' db in the dropdown and try it that way.

If that doesn't do it, use sp_who to find what spid is accessing the DB and kill it.

Upvotes: 13

Related Questions