9

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

Bring DB Online Error

Error looking at SQL Server error log from SSMS

2. SQL Server Log

Permissions for login

3. Server Roles of My Admin Account

Error adding login to sysadmin role

4. Failure to add SysAdmin Role to Admin Account

1:

1 Answer 1

16

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.