SQL Server (2012) grant right to regular user to set database in read-only mode i would like to grant right to specific regular users on an sql server 2012 the right to set the database from read-write mode into read-only mode and vice versa. but i would not like to grant them any other administrative privileges.

how do i achieve that? do i have to create a stored procedure or a new database schema? or is there a schema that incoorporates that already?

the code needed to set the database into read-only mode goes as follows:

ALTER DATABASE database-name SET READ_ONLY


or

ALTER DATABASE database-name SET READ_WRITE


cheerio :)
The simple answer is:

GRANT ALTER ON database::[yourDB] TO [yourLogin];


That may, however, give more permissions than you intend. If you want to lock that down, you could look into module signing. That is, you'd create a stored procedure that, given a database name (or perhaps none at all depending on your use case) flips the state. Then, you "bless" the procedure such that it can run in an elevated context.