ALTER DATABASE - Access Options

by Andy on May 14th, 2008

Last night I restored a database and upon completion I found it to be in ‘Restricted User’ mode on the tree in SSMS 2005.  Having been too used to SQL 2000 Enterprise Manager rather than SSMS (and given that I was working into my 12th hour of the day!) I couldn’t recall how to remove the restriction, so here’s a brief explanation of the different access options and how to quickly change this within SSMS:

SINGLE_USER - Only a single user can be in the database at a time.

RESTRICTED_USER – This is the same as the old dbo use only setting. Only users in the db_owner role (or sysadmin role) can use the database when this value is set.

MULTI_USER - Normal access and any user with any access to the database can use the database.

For some settings (including these user access options,) you can specify a termination clause which will terminate connections to the database (other than the one executing the command) before changing the setting.

Also, if you want to set the database to read only (or back to read write,) you can use READ_ONLY and READ_WRITE.

How to set:

Right click database name > Properties > Options (from Select a page) > State (bottom of the list) > Restrict Access > here you can select your choice.

Tweet This Post

How to Kill All Processes That Have Open Connection in a SQL Server 2005 Database

by Andy on May 8th, 2008

Most SharePoint folks with at some point need to take their SharePoint databases offline (especially for development and test environments).

However, it’s quite common for one of more of your databases to ‘appear’ to be in process of going offline which may eventually fail.

If this happens to you then it’s more than likely that you have connections that require dropping. In SQL 2000 you would receive a prompt to drop all connections before taking a database offline, but not in SQL 2005.

The quickest way I found to drop all database connections and take the database offline is to [a] Place the database into Single User Mode [here's my related article on this] and then [b] run the following T-SQL (be careful with the inverted commas!):

USE master
go

DECLARE @dbname sysname

SET @dbname = ‘name of database you want to drop connections from’

DECLARE @spid int
SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname)
WHILE @spid IS NOT NULL
BEGIN
EXECUTE (’KILL ‘ + @spid)
SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname) AND spid > @spid
END

Tweet This Post