0

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

Thanks in advance

4

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;
0

You cannot make a change to a READ_ONLY database. You will have to set database to READ_WRITE before making any change.

0

If the database is logshipped and your primary is not read_only but the secondary is, you would have to make the change on the primary for it to affect the secondary. Answer by @SqlWorldWide is 100% correct.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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