Forum Discussion

Victor_Sotnikov's avatar
Victor_Sotnikov
Copper Contributor
Jun 28, 2024

То allow a user to change Read-Only flag of a DB

I have SQL Server 2012 Enterprise. I have an SQL user that is not sysadmin on the server. The user is allowed to read and write the DB; but it cannot change Read-Only flag (Database Properties -> Options -> Database  Read-Only) of the  DB. What should be done to allow the user to change Read-Only flag?

  • bandaruajeyudu's avatar
    bandaruajeyudu
    Copper Contributor
    Jul 04, 2024
    Connect to SQL Server using an account with sufficient privileges (such as a sysadmin or a user with the ability to grant database permissions).

    Execute the following SQL command to grant the ALTER DATABASE permission to the user on the specific database:

    sql
    Copy code
    USE [YourDatabaseName];
    GO
    GRANT ALTER ON DATABASE::[YourDatabaseName] TO [YourUserName];
    GO
    Replace [YourDatabaseName] with the name of your database and [YourUserName] with the username of the user you want to grant permissions to.

    Example
    Suppose your database name is MyDatabase and the username is User1. You would run the following commands:

    sql
    Copy code
    USE [MyDatabase];
    GO
    GRANT ALTER ON DATABASE::[MyDatabase] TO [User1];
    GO
    Explanation
    The USE [YourDatabaseName]; command switches the context to the specified database.
    The GRANT ALTER ON DATABASE::[YourDatabaseName] TO [YourUserName]; command grants the ALTER DATABASE permission to the specified user, allowing them to change database properties such as the Read-Only flag.

Resources