TravisDBA (1/15/2011)
When restoring a database or doing any kind of structural maintenance for that matter, the only seesion that should have access to the database is the restore or maintenance session itself. So, setting the database iinto RESTRICTED_USER is not really recommended for database restore or maintenamce operations. This is because that mode allows more than one person to connect to the database if more that one person are members in the sysadmin, db_owner, or dbcreator roles for that database on your db server.. On many boxes, there is usally more than one member in those roles. So, that is not really what you want here. What you should use for any database maintenance operations is SINGLE_USER mode. That makes absolutely sure no one else can access that sataabase while you have it down for maintenance. Much safer. 😀ALTER DATABASE database SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
First, there is no good reason for an account belonging to a BI user, application, etc. to be a member of sysadmin or db_owner. In fact, the account used by the SQL Server Agent to run jobs, shouldn't be a member of sysadmin either, just a member of one of the more limited built in SQL Agent roles with whatever additional rights are needed to do it's work.
The problem with SINGLE_USER mode is that if the sysadmin logs off temporarily while still in single mode, then there is a window of opportunity for a user, application, scheduled job, etc. to login and lock everyone else out. I'd want to make sure that everyone, other than the sysadmins, are totally blocked out until all the work is completed, and that could span hours even into the next day. There may also be a need for another sysadmin to login and check on the status of an operation started by another sysadmin. The sysadmins can avoid stepping on each other's toes by coordinating ahead of time when there is some special maintenance going on. For these reasons, I'd prefer to go with RESTRICTED_USER mode.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho