Tag Archives: SQL Server Management Studio

Different ways to make a table read only in a SQL Server database


In some cases there may be a need to make a SQL Server table read only. There are several different options for doing this and in this tip we cover various ways that you can make a table read only in a SQL Server database.



Dedicated Administrator Connection

DAC: Dedicated Administrator Connection feature is available from the SQL Server 2005. It is available in all the higher editions by default except express edition. DAC will be useful when SQL Server is not responding any connections; in such kind of situation DBA will connect through the DAC and troubleshoot/fix the issue.

You can execute below kind of command for the initial troubleshooting.

-- Locking Info
SELECT * FROM sys.dm_tran_locks
-- Running Sessions
SELECT * FROM sys.dm_exec_sessions
-- Requests Status
SELECT * FROM sys.dm_exec_requests
--Open Sessions
--To get the SQL Text
--To terminate the curlprit process

DAC is disabled by default, it is a good practice to enable the DAC. You can enable the DAC using by executing below query.

Use master
--0 = Allow Local Connection,
--1 = Allow Remote Connections*/
sp_configure 'remote admin connections', 1

You can connect using DAC on of the following method.
Command Prompt
SQL Server Management Studio

Using Command Prompt: Use SQLCMD utility to connect to SQL Server as below.
-A argument is to specify the DAC connection.
-S argument is to specify the server name.
-d argument is to specify the database name.
-E argument is for windows connection with integrated security true

Using Management Studio: Write ADMIN: before the server name in management studio connection window. It will give you the DAC connection.

To enable the DAC connection in SQL Server express edition add ;-T7806 trace flag as startup parameter.

Go into configuration manager — right click on SQL Server Service and select properties — go into advanced tab and add the trace flag ;-T7806. Once done restart the SQL Server Services.

SQLWB.exe (SQL2k5) and SSMS.exe(SQL2K8)

SQLWB (sqlwb.exe) /SSMS(SSMS.exe) is the executable file that launches SQL Server Management Studio. I am usually using it to launch the SQL Server management studio instead of navigating through Start -> All Programs -> SQL Server 2005 -> SQL Server Management Studio.

Above command will launch the SQL Server 2005 Management Studio. You can launch the SQL2k8 management studio using SSMS.exe

You can also pass below list of argument with it, I am executing below command to see the list of parameters.

c:\sqlwb.exe -?