March 6, 2007 at 1:17 pm
I'm working on an sql server 2000 database.
According to BOL, setting a RESTRICTED_USER WITH ROLLBACK IMMEDIATE breaks unqualified connections immediately. All incomplete transactions are rolled back. Unqualified connections are connections for users who are not members of the db_owner fixed database role and dbcreator and sysadmin fixed server roles.
I have users who are members of dbo_owner fixed database role and dbcreator role and sysadmin fixed server roles but every time I run set Restricted_user they are kicked off the database.
Any ideas why?
Thank you.
March 6, 2007 at 1:41 pm
That is not what it says in Books Online. It doesn't say that it does not break their connection.
From SQL Server 2000 books Online:
Controls which users may access the database. When SINGLE_USER is specified, only one user at a time can access the database. When RESTRICTED_USER is specified, only members of the db_owner, dbcreator, or sysadmin roles can use the database. MULTI_USER returns the database to its normal operating state.
The SQL 2005 Books Online is more specific about what actually happens:
"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. All connections to the database are disconnected in the timeframe specified by the termination clause of the ALTER DATABASE statement. After the database has transitioned to the RESTRICTED_USER state, connection attempts by unqualified users are refused."
March 6, 2007 at 1:55 pm
Thanks Michael,
Below is a copy of Books Online. I have bolded out some pointers to the fact that there are qualified and unqualified users During and After altering the database to restricted_user.
Thank you.
Jo
SINGLE_USER | RESTRICTED_USER | MULTI_USER
SINGLE_USER allows one user at a time to connect to the database. All other user connections are broken. The timeframe for breaking the connection is controlled by the termination clause of the ALTER DATABASE statement. New connection attempts are refused. The database remains in SINGLE_USER mode even if the user who set the option logs off. At that point, a different user (but only one) can connect to the database.
To allow multiple connections, the database must be changed to RESTRICTED_USER or MULTI_USER mode.
RESTRICTED_USER allows only members of the db_owner fixed database role and dbcreator and sysadmin fixed server roles to connect to the database, but it does not limit their number. Users who are not members of these roles are disconnected in the timeframe specified by the termination clause of the ALTER DATABASE statement. Moreover, new connection attempts by unqualified users are refused.
MULTI_USER allows all users with the appropriate permissions to connect to the database. MULTI_USER is the default setting.
The status of this option can be determined by examining the UserAccess property of the DATABASEPROPERTYEX function.
WITH <termination>
The termination clause of the ALTER DATABASE statement specifies how to terminate incomplete transactions when the database is to be transitioned from one state to another. Transactions are terminated by breaking their connections to the database. If the termination clause is omitted, the ALTER DATABASE statement waits indefinitely, until the transactions commit or roll back on their own.
ROLLBACK AFTER integer [SECONDS]
ROLLBACK AFTER integer SECONDS waits for the specified number of seconds and then breaks unqualified connections. Incomplete transactions are rolled back. When the transition is to SINGLE_USER mode, unqualified connections are all connections except the one issuing the ALTER DATABASE statement. When the transition is to RESTRICTED_USER mode, unqualified connections are connections for users who are not members of the db_owner fixed database role and dbcreator and sysadmin fixed server roles.
ROLLBACK IMMEDIATE
ROLLBACK IMMEDIATE breaks unqualified connections immediately. All incomplete transactions are rolled back. Unqualified connections are the same as those described for ROLLBACK AFTER integer SECONDS.
NO_WAIT
NO_WAIT checks for connections before attempting to change the database state and causes the ALTER DATABASE statement to fail if certain connections exist. When the transition is to SINGLE_USER mode, the ALTER DATABASE statement fails if any other connections exist. When the transition is to RESTRICTED_USER mode, the ALTER DATABASE statement fails if any unqualified connections exist.
March 6, 2007 at 2:20 pm
Notice that is does not actually say that it does not break the connection of of qualified users, it only says that the breaks the connections of unqualified users.
I agree that what it says is misleading, and that is why I posted the quote from SQL 2005 BOL.
March 6, 2007 at 2:24 pm
SQL Server Books Online had an update in January 2005, which can be downloaded from:
The MSDN SQL Server documentation for RESTRICTED_USER agrees with Jo Mong's post.
http://msdn2.microsoft.com/en-us/library/aa933082(SQL.80).aspx
SQL = Scarcely Qualifies as a Language
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply