Database offline worked fine, database online didn't!

I was browsing the SQL Server subreddit earlier where someone had posted a problem where they'd been able to take a database offline but couldn't bring the database back online via a script or the UI in SSMS (full thread here).

There's a bit of a back story; all the DBA's have left the business (facepalm) so a non-DBA has been left with the admin type tasks. Secondly the reason the database was being taken offline was to take physical backups of the databases mdf and ldf files (double facepalm).

Anyway, on the issue itself. My first thought was permissions, because it always is...or at least we can't blame DNS this time. But I had a slight doubt, surely if you had permissions to take a database offline you can bring it back?

Well, nope.

Being a member of the db_owner role will give you the required access to take a database offline. I'm going to test it on my machine as we go along:

CREATE LOGIN [supertester] WITH PASSWORD=N'MEG4PASSword.77', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

Then I'll change database context to my imaginatively named Test database and add the supertester user and add it to the db_owner role:

USE Test

GO

CREATE USER [supertester] FOR LOGIN [supertester];

EXEC sp_addrolemember N'db_owner', N'supertester';

I've logged into SSMS as the supertester login, the first thing I want to try is taking the database offline but I'm going to select to Drop All Active Connections:

I get the following error on VIEW SERVER STATE permissions being denied:

However, if I run the following T-SQL command the database successfully goes offline:

ALTER DATABASE Test SET OFFLINE WITH ROLLBACK IMMEDIATE; 

So, supertester has been able to offline the Test database, now to bring it back:

ALTER DATABASE Test SET ONLINE;

Unfortunately I'm greeted by the following red text:

Msg 5011, Level 14, State 9, Line 1

User does not have permission to alter database 'Test', the database does not exist, or the database is not in a state that allows access checks.

Msg 5069, Level 16, State 1, Line 1

ALTER DATABASE statement failed.

Oh dear...or words to that effect, db_owner role members can take databases offline but not bring the databases back online again. In this case it's going need elevated permissions, either ALTER ANY DATABASE being granted or maybe sysadmin role membership, especially if they're going to be taking on more admin type activities in the future.

The final note has to be about this whole scenario. I really feel for accidental/reluctant DBA's who are tasked (or rather thrown in to the deep end) with all kinds of activities without any support whatsoever. This is a perfect example where someone has been following instructions (admittedly they make no sense) without any real understanding and through no fault of their own has ended up in what could be a critical situation.

Yes, let's of people jumped into the thread to help which is awesome, and you know it's such a great thing about the SQL community that people are always willing to help but this is something that could have been avoided.

Finally I'll drop the supertester user and login because they're always up to no good.