quote:

---

Originally posted by denis_the_thief

If I am not mistaked, single user mode is not a start-up feature nor is it at the Server level. It is at the Database level.

Management Studio is just a Client Interface/Application. If you have permission to do something, you have this permission regardless of which Client tool you are using.

I use Single User Mode when I need to apply a lot of upgrade scripts, to prevent others from using the Database during the upgrade process. And yes, I will do it all in the Management Studio.

---

quote:

---

Originally posted by denis_the_thief

I take part of that back, you can start the Server in single-User Mode. (The single-user Database is also an option)

I guess I have never tried connecting with the Server in Single User Mode. But I did read, you have to turn off SQL Server Agent since it can use that one connection.

And sorry, I don't use Sinlge User mode to update the Database, I use Restricted mode - sa only. Maybe that option will work for you.

---

quote:

---

Originally posted by denis_the_thief

On a Database Level, here is how in SSMS to set to single-user mode.

Select Database -> Properties -> Options

And then at the bottom is "Restrict Access"

---

quote:

---

Originally posted by tkizer

Why are you trying to start SQL in single-user mode? SSMS uses multiple connections, so it can be hard to use when in single-user mode. Typically you'd use sqlcmd.exe.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

---

quote:

---

Originally posted by lionofdezert

By single user mode you must mean, DAC-Dedicated Admin Connection. Yes you can SSMS when connected as DAC http://connectsql.blogspot.com/2012/10/sql-server-placing-alert-for.html


--------------------------
http://connectsql.blogspot.com/

---

quote:

---

Originally posted by sgondesi



Sorry sir. I dont know when to use single user mode and what it was exactly.

Actually when i am facing problem in executing the below command,

"ALTER DATABASE IsolationDB SET READ_COMMITTED_SNAPSHOT ON ;"

Then one DBA here in this forum suggested me to start server in single user mode and try executing the command.

So i am trying to do that. That's all. I am not aware of single user mode, just came across DAC while learning the architecture but i dont know how to make use of it.

---

ALTER DATABASE IsolationDB SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE

DECLARE @spid varchar(10)

SELECT @spid = spid
FROM master.sys.sysprocesses
WHERE dbid = DB_ID('IsolationDB')

WHILE @@ROWCOUNT <> 0
BEGIN
 EXEC('KILL ' + @spid)

 SELECT @spid = spid
 FROM master.sys.sysprocesses
 WHERE 
  dbid = DB_ID('IsolationDB') AND
  spid > @spid 
END

ALTER DATABASE IsolationDB SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE IsolationDB SET MULTI_USER;

quote:

---

Originally posted by tkizer

quote:

---

Originally posted by sgondesi



Sorry sir. I dont know when to use single user mode and what it was exactly.

Actually when i am facing problem in executing the below command,

"ALTER DATABASE IsolationDB SET READ_COMMITTED_SNAPSHOT ON ;"

Then one DBA here in this forum suggested me to start server in single user mode and try executing the command.

So i am trying to do that. That's all. I am not aware of single user mode, just came across DAC while learning the architecture but i dont know how to make use of it.

---

You do not need to put the server in single-user mode to execute that query. You do need to disconnect all sessions from that database though.

This should do it if the connected users aren't members of sysadmin or db_owner roles:

ALTER DATABASE IsolationDB SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE IsolationDB SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE IsolationDB SET MULTI_USER;

If that doesn't work, then use this:

ALTER DATABASE IsolationDB SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE

DECLARE @spid varchar(10)

SELECT @spid = spid
FROM master.sys.sysprocesses
WHERE dbid = DB_ID('IsolationDB')

WHILE @@ROWCOUNT <> 0
BEGIN
 EXEC('KILL ' + @spid)

 SELECT @spid = spid
 FROM master.sys.sysprocesses
 WHERE 
  dbid = DB_ID('IsolationDB') AND
  spid > @spid 
END

ALTER DATABASE IsolationDB SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE IsolationDB SET MULTI_USER;

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

---