If the Database is in Readonly mode how can we drop/remove those unused roles

Question

In SQL Server, if the Database is in Read_only mode, can we drop/remove unused roles? Please let me know.

Thanks in advance

Asked2017-04-17byBharath Kuamr reddy

Answer

If a database is in READ_ONLY mode, it is completely read-only. The read-only-ness is not limited to user data, but anything contained in the database. This means that both DML commands (ie, INSERT, UPDATE, DELETE), and DDL commands (ie, CREATE, ALTER, DROP) are prohibited.

If the database is a read-only replica of an Availability Group, or a read-only log-shipping secondary, then you would need to drop the roles on the primary replica, and the normal data movement will replicate those changes to your read-only secondary. It is not possible for the secondary to have different security roles than the primary.

If your database is a stand-alone database that has been put into READ_ONLY, then you would need to set the database to read-write before dropping the unused role(s), then set the database back to read-only:

USE master
ALTER DATABASE [<MyReadOnlyDatabase>] SET READ_WRITE;
GO
USE [<MyReadOnlyDatabase>];
DROP ROLE [<OldUnusedRole>];
GO
USE master;
ALTER DATABASE [<MyReadOnlyDatabase>] SET READ_ONLY;

Answered2017-04-17byAndy Mallon