Create User and assign permission failed for user on ReadOnly RO Database

There will be scenarios due to business needs you have to set your database to ReadOnly(RO) mode. When the database is RO mode, you may sometimes want to create a User on that RO database and assign some permission to it. But this fails giving error "Failed to update database "" because the database is read-only."

This is because when a Database is in ReadOnly Mode, you cannot create user or assign permission to it. To achieve this you need to bring back the database to ReadWrite mode, create user assign permission and then again set the database to RO mode.

Below is a small script to achieve all this task in a single Go. I have referred a database called "Test" which is RO database, you need to replace this "Test" with your RO Database name.

USE [master]

GO

--Set User to Single User Mode to Avoid any Connections to this database

ALTER DATABASE [Test] set single_user with rollback immediate

go

--Set Database to ReadWrite

ALTER DATABASE [Test] SET READ_WRITE WITH NO_WAIT

GO

--Create User and Assign Permission

USE [Test]

GO

CREATE USER [YourUserName] FOR LOGIN [YourUserName]

GO

USE [Test]

GO

EXEC sp_addrolemember N'db_datareader', N'YourUserName'

GO

USE [Test]

GO

EXEC sp_addrolemember N'db_datawriter', N'YourUserName'

GO

USE [master]

GO

--Set Database to ReadOnly Again

ALTER DATABASE [Test] SET READ_ONLY WITH NO_WAIT

GO

--Set Database back to MultiUser Mode

ALTER DATABASE [Test] set Multi_user with rollback immediate

go