(restricted user) after restore
Question:hey all, when i restored my db from production to a virtual pc, (restricted user) showed up in parens next to the db. can someone please explain this and how i need to resolve it?
Answer:
Right-Click on your database, and select New Query
to set to Multi user
ALTER DATABASE databasename SET MULTI_USER To set back to Restricted ALTER DATABASE databasename SET RESTRICTED_USER
4 comments:
http://www.sqldev.org/sql-server-database-engine/sql-server-2005-restricted-user-mode-problem-80320.shtml
Sql server 2005 restricted user mode problem?
One of my sql server 2005 database is in restricted user mode, how can I change it to normal ? what is restricted user mode?
Thanks.
Jake Wardley Wednesday, October 28, 2009 1:44 AM
RESTRICTED_USER allows for only members of the db_owner fixed database role and dbcreator and sysadmin fixed server roles to connect to the database, but does not limit their number.
You can use this command to bring the db back to normal (multi user mode ),
alter
database DBNAME set multi_user
http://www.codeease.com/sql-server-restricted-user.html
»
SQL Server Restricted User ?
January 25, 2011, 6:36 pm
Sometimes you might find your SQL Server database is in “Restricted User” mode: like following:
What does “Restricted User” means ?
First, let us read MSDN information:
Restrict Access
Specify which users may access the database. Possible values are:
* Multiple
The normal state for a production database, allows multiple users to access the database at once.
* Single
Used for maintenance actions, only one user is allowed to access the database at once.
* Restricted
Only members of the db_owner, dbcreator, or sysadmin roles can use the database.
So how to disable Restricted ?
1: Run the following commands:
ALTER DATABASE database SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE database SET MULTI_USER
GO
2: Using SSMS right click on the database — properties –option in the right pane you can find restrict access
SINGLE_USER = only one db_owner, dbcreator, or sysadmin user at a time
RESTRICTED_USER = only members of db_owner, dbcreator, and sysadmin roles
MULTI_USER = all users
(OR)
SELECT DATABASEPROPERTYEX(‘’,'UserAccess’)— (Indicates which users can access the database)
alter database set multi_user
http://www.bigresource.com/MS_SQL-restricted-user-mode-q8EeRCri.html
http://www.sqlusa.com/bestpractices/singleuser/
http://www.blackwasp.co.uk/SQLRestrictedUser.aspx
http://www.eggheadcafe.com/software/aspnet/30818593/restricted-user-after-restore.aspx
hey all,
when i restored my db from production to a virtual pc, (restricted user)
showed up in parens next to the db.
can someone please explain this and how i need to resolve it?
thanks,
rodchar
Don't know why it happened, but I would try altering the database and
set it to multi user mode:
ALTER WriteDBNameHere SET MULTI_USER
Adi
http://aliparvaresh.com/archive/2009/07/18/solve-restricted-user-issue-in-sql-server-2005.aspx
Solve Restricted User Issue in SQL Server 2005
Simply run the following the commands against the database which is showing as 'Restricted User':
ALTER DATABASE database SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE database SET MULTI_USER
GO
Post a Comment