Creating Read_Only Database in SQL Azure?

Question

How can I create / alter/modify database into read_only in sql azure.

Answer 1

I do not understand what you mean by alter/modify database into read_only but:

You can create users that have only data read permission (with db_datareader role)

step1: create a login

create login parasdoshicom with password=’Passw0rd’

go

step 2: create user

create user parasdoshicom from login parasdoshicom

go

step 3: assign role to user

EXEC sp_addrolemember N’db_datareader’,N’parasdoshicom’

go

More details:

http://parasdoshi.com/2011/11/04/how-to-create-users-with-limited-access-in-sql-azure/

http://msdn.microsoft.com/en-us/library/ee336235.aspx

---

Paras Doshi (Blog: ParasDoshi.com | Twitter: @Paras_Doshi )

Answer 2

he's probably referring to the ALTER DATABASE SET READ_ONLY option in SQL Server

ALTER DATABASE AdventureWorks2012
SET READ_ONLY;

this is not available on SQL Azure.

see: http://msdn.microsoft.com/en-us/library/ff394109.aspx on partially supported statements.

paras' suggestion is a good workaround for this limitation.