This Q&A was previously posted on the Stack Exchange Network of websites.
This content is licensed under the Creative Commmons Attribution-Sharealike (CC BY-SA) license. As the author of these answers, this archive serves as a canonical location for permalinks, which will outlive Stack Overflow. See all my answers by looking at the Stack Exchange post category.
MS SQL Express 2016 on Amazon AWS: I Can Take Database Offline but Can’t Bring It Online
Question
I can take databases offline (via GUI) but can’t bring them back online. The server details are as follow:
RDBMS: MS SQL 2016 Express
Host: Amazon AWS/RDS Free Tier
Details/History of the Problem
A few months ago, I created a db instance on Amazon AWS and at the time of creation, the ‘master/admin’ account was setup via the AWS/RDS web page. With this ‘admin’ account, I have created several databases on that instance without any problems.
Over the past few months, I have used this ‘admin’ account to change several databases to contained databases. I do this so that I can setup contained users. I have also done this several times on this server instance with the same admin account with no problems.
Last night, I had just created a new database via this admin account. I then tried to set this new database as a contained database and the process failed. The dialog box error message stated among other things “please try again later”.
After the 3rd failed attempt, I decided to take the database offline (via the GUI in SSMS). I did this in a bid to force close any possible open processes or connections that might be on this new database. That worked. However, I have not been able to bring it back online. I have tried via the GUI and also via a query and it keeps failing.
I have then checked the server roles assigned to this ‘admin’ account. It is not part of sysadmin role. As I understand, the ‘sysadmin’ role can do absolutely anything on the db instance. I reckon my admin account is not of this sysadmin role because it is meant for the in-house DBAs at Amazon AWS. I have tried to add it as sysadmin but it fails.
To ensure that my ‘admin’ account is the problem, I have taken another database offline (it’s empty). It went offline but it is also failing to come back online.
What could be the problem? Please help. Note that my skill level is very very low and I’m learning as I go along.
The server logs don’t show anything useful. I have attached screenshots.
Error bringing database online in SSMS
Error looking at SQL Server error log from SSMS
Permissions for login
Error adding login to sysadmin role
asked 2020-03-01 by Theo Fitchner
Answer
You are correct: your admin account is not sysadmin, but a less powerful account. This is necessary to ensure that AWS is able to limit you from doing EVERYTHING to this instance, and escaping security boundaries.
Setting a database online
As part of your admin account, you have access to set databases offline, but bringing a database back online requires sysadmin (which you don’t have). To work around this permission restriction, AWS provides a stored procedure to bring databases online. You’ll need to use the rds_set_database_online procedure in the rdsadmin database
EXEC rdsadmin.dbo.rds_set_database_online 'MyOfflineDatabase';
This procedure is fully documented by AWS here.
Reading the error log
Similarly, you cannot use xp_readerrorlog on RDS SQL Server because it requires sysadmin. Instead, AWS provides THE stored procedure rds_read_error_log
EXEC rdsadmin.dbo.rds_read_error_log;
This procedure is fully documented by AWS here.
answered 2020-03-01 by Andy Mallon