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.
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
asked 2017-04-17 by Bharath 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;
answered 2017-04-17 by Andy Mallon