Making a SQL Server Database Read-Only and Write-Only

Making a SQL Server Database Read-Only

There are many situations where it is important that users are unable to modify the contents of a database. For example, if data is to be migrated to another server or for reporting purposes. This tip explains how to set a database to a read-only mode.

ALTER DATABASE Command

The ALTER DATABASE command allows a database administrator to modify SQL Server databases and their files and filegroups. This includes permitting the changing of database configuration options.

Setting a Database to Read-Only

When you need to ensure that the data is a database is not modified by any users or automated processes, it is useful to set the database into a read-only mode. Once read-only, the data can be read normally but any attempts to create, updated or delete table rows is disallowed. This makes the read-only mode ideal when preparing for data migration, performing data integrity checking or when the data is only required for historical reporting purposes.

To make a database read-only, the following command is used:

ALTER DATABASE database-name SET READ_ONLY

Setting a Database to Read-Write

If the read-only requirements for the database are temporary, you will need to reset the configuration option following any procedures undertaken. This is achieved with a small modification to the ALTER DATABASE statement to indicate that the database should return to a writeable mode.

ALTER DATABASE database-name SET READ_WRITE

run asp.net web application from pendrive using iis server , run website from pendrive, run website from pendrive using iis

run an asp.net website or web application from pen drive using iis server .

please follow these steps

1) configure iis in computer

2) copy web application and database in pendrive

3) connect application from iis as well as attach database mdf file in sql server

4) set connection string in web config using sql server name

5) only one issue will come , that will be like , when u will remove pen drive and reconnect it again then it will not work and will give error.

reason of error : actually what happend when u remove the pendrive and reconnect it then sql server  does not recognize the sql server database files from that path,

so we need to detach the database from sql server then reattach it for make it work.

but its very time consuming task to do it mannualy so what we will do is

we will put a code in our application start up that will do this by code.

means we will detach the database file from c# code.

 try
            {
                SqlConnection sqlConnection1 = new SqlConnection("Server=COMPUTER-001;Database=master;Integrated Security=true");
                SqlCommand cmd = new SqlCommand();


                cmd.CommandText = "ALTER DATABASE EasyInventory  SET SINGLE_USER WITH ROLLBACK IMMEDIATE   EXEC sp_detach_db 'EasyInventory'";
                cmd.CommandType = CommandType.Text;
                cmd.Connection = sqlConnection1;

                sqlConnection1.Open();

                cmd.ExecuteNonQuery();
                // Data is accessible through the DataReader object here.

                sqlConnection1.Close();


                string fullPath = "D:\\NewEasyInventory\\Database\\EasyInventory.mdf";
                DirectoryInfo dInfo = new DirectoryInfo(fullPath);
                DirectorySecurity dSecurity = dInfo.GetAccessControl();
                dSecurity.AddAccessRule(new FileSystemAccessRule(new SecurityIdentifier(WellKnownSidType.WorldSid, null), FileSystemRights.FullControl, InheritanceFlags.ObjectInherit | InheritanceFlags.ContainerInherit, PropagationFlags.NoPropagateInherit, AccessControlType.Allow));
                dInfo.SetAccessControl(dSecurity);

                string fullPath2 = "D:\\NewEasyInventory\\Database\\EasyInventory_log.ldf"; ;
                DirectoryInfo dInfo2 = new DirectoryInfo(fullPath2);
                DirectorySecurity dSecurity2 = dInfo2.GetAccessControl();
                dSecurity2.AddAccessRule(new FileSystemAccessRule(new SecurityIdentifier(WellKnownSidType.WorldSid, null), FileSystemRights.FullControl, InheritanceFlags.ObjectInherit | InheritanceFlags.ContainerInherit, PropagationFlags.NoPropagateInherit, AccessControlType.Allow));
                dInfo2.SetAccessControl(dSecurity2);
            }

            catch (Exception ex)
            {
            }


here we have two codes,

one is to detach the sql server database file

second is to give everyone permission on database mdf and ldf files in pendrive.

so u need to set the correct path of mdf and ldf files on ur pen drive

for further help reply.

thanks