Fix XACT_S_READONLY 0x4D002: Read-Only Transaction

XACT_S_READONLY means your transaction tried to write but the database or session is read-only. Start with the quickest check—your connection string or SSMS session—then work up to database settings.

What's Happening Here

You just tried to run an INSERT, UPDATE, or DELETE inside a transaction, and SQL Server slapped you with XACT_S_READONLY (0x4D002). The message says the method call succeeded because the transaction was read-only—but that's misleading. What it really means is your transaction can't write a single byte.

I've seen this pop up in three common places:

A developer accidentally set ReadOnly=true in a connection string (classic).
A DBA marked the database as read-only for a maintenance window and forgot to flip it back.
Someone used SET TRANSACTION READ ONLY in a session that later tried to write.

Let's walk through the fixes from quickest to most involved. Stop as soon as your query runs.

Fix 1: Check Your Connection String (30 seconds)

If you're getting this from an application, the first place to look is the connection string. Open your app's config file—appsettings.json for .NET Core, web.config for older ASP.NET, or whatever your framework uses.

Search for ReadOnly. If you see ReadOnly=true or ReadOnly=True, that's your culprit. Remove that parameter entirely, or set it to false.

Here's a typical example you might find:

Server=myServer;Database=myDb;User Id=sa;Password=secret;ReadOnly=true;

Change it to:

Server=myServer;Database=myDb;User Id=sa;Password=secret;

After you make the change, restart your application. You should be able to write again. If you're using a connection pooling library, also clear the pool (in .NET, run SqlConnection.ClearAllPools() once) because old read-only connections might linger and get reused.

Fix 2: Check the Database State (5 minutes)

If the connection string is clean, the database itself might be read-only. This happens more than you'd think. Someone runs a backup or a restore, sets the database to read-only for safety, then forgets to switch it back.

Open SQL Server Management Studio (SSMS), connect to your server, and run this query:

SELECT name, is_read_only FROM sys.databases;

Look at the row for your database. If is_read_only returns 1, that's the problem.

To fix it, you need to change the database back to read-write. Run this, replacing YourDatabaseName with the actual name:

ALTER DATABASE YourDatabaseName SET READ_WRITE;

You'll get a message like "Commands completed successfully." That's it. Try your write operation again.

One gotcha: if the database is in a mirroring or availability group, you might not be allowed to change this directly. In that case, check the primary replica—the secondary is always read-only.

Fix 3: Look at Your Transaction Isolation Level (15+ minutes)

This is the sneaky one. You might have explicitly set a read-only transaction in your code or session. Some ORMs do this automatically for certain operations.

First, check if you're currently in a read-only transaction. Run this in the same session where you got the error:

SELECT transaction_isolation_level, is_read_committed_snapshot_on FROM sys.dm_exec_sessions WHERE session_id = @@SPID;

That doesn't directly show read-only, but if you suspect your code is doing it, search your codebase for SET TRANSACTION READ ONLY. If you find it, remove it. That T-SQL statement makes the entire transaction read-only, and any write attempt will give you this error.

Some ORMs—like Entity Framework with certain database providers—might start a read-only transaction when you call a query that uses AsNoTracking(). If you're using EF, check your DbContext configuration for something like:

optionsBuilder.UseSqlServer(connectionString, o => o.UseTransaction(...));

Actually, the more common culprit is in the connection string parameter ApplicationIntent=ReadOnly. That's used for read-only routing to availability group replicas. If you have that, remove it unless you really want to connect to a read-only replica.

Let's also verify you're not hitting a view or a table that's defined as read-only. Check the table's properties:

SELECT OBJECTPROPERTY(OBJECT_ID('YourTableName'), 'IsReadOnly') AS IsReadOnly;

If that returns 1, the table is marked read-only (unusual but possible). You'll need to alter the table's filegroup or rebuild it. But honestly, that's rare—the connection string and database state cover 95% of cases.

When All Else Fails: Trace It

If you've checked all three and still get the error, capture a trace or extended events session to see what command is actually being executed. Sometimes the error comes from a stored procedure that has SET TRANSACTION READ ONLY inside it. Search your stored procedures:

SELECT OBJECT_NAME(object_id), definition FROM sys.sql_modules WHERE definition LIKE '%READ ONLY%';

If you find one, that's your culprit. Modify that procedure to remove the read-only declaration.

One more thing—if you're using a linked server, the target database on the remote server might be read-only. You'd see the same error but from a different layer. Run Fix 2 on the remote server.

Why This Error Message Is Confusing

The wording "method call succeeded" is the worst. It makes it sound like nothing went wrong, but it's actually a warning that your transaction couldn't do what you asked. SQL Server is saying, "Hey, I executed your BEGIN TRANSACTION, but I'm not going to let you write because the environment says read-only."

Once you understand that, the fix becomes straightforward: find where read-only is set and turn it off. Start with the connection string—it's the fastest and most common. Move to the database state next. Then dig into transaction settings. You'll have it running in no time.