How to Make a Table Read Only in SQL Server

How to make a table Read Only in SQL Server?

1. Trigger with rollback trans
2. Read only filegroup
3. Don't grant insert/update/delete permissions

Number 3 is probably best practice. For example, if your connection is db_owner for example then the trigger can be disabled the trigger or move the table to a different filegroup anyway.

Create a Temporary Table in SQL Server with Read only access

A temp table is automatically deleted when you close a connection. If you are doing this work within a Stored Procedure or from SSMS then this doesn't affect you, but if you are doing this from .NET (or another programming language) and you are connecting/disconnecting, or loading it from one script and trying to read it from a report (for example), then no, the table won't be visible or exist for any session/connection other-than the one that created it.

If you need it for a few minutes, and you intend to connect/disconnect (more than zero times), you should create a table. You can do it under your own schema or pick a name that sounds temporary.

Ex. If I am connecting as Domain\DataUser1, my schema is [Domain\DataUser1]

SELECT UserName FROM LoggingTable
INTO TABLE [Domain\DataUser1].TempTable1
--I need those square brackets around my schema because of the \

This will create a table under my user's schema. I can query it from another connection

SELECT * FROM [Domain\DataUser1].TempTable1

Oh. Don't forget to drop the temp table when you are done:

DROP TABLE [Domain\DataUser1].TempTable1
-- or, if you intend to keep using it --
TRUNCATE TABLE [Domain\DataUser1].TempTable1

Best way to make all tables read only

alter database current set read_only

To reverse, or if you want to modify views, run

alter database current set read_write

No permissions-based solution is effective against the database owner.

Make a database table read only temporarily (and not raising errors)

Nevermind, I think I found a solution. Removing the ROLLBACK TRANSACTION and putting a non-sense line of code between BEGIN and END did the trick.

CREATE TRIGGER CacheNoInsert ON CacheTable
INSTEAD OF INSERT, UPDATE, DELETE
AS
BEGIN
    PRINT 'doesntMatter'
END

But I hope the transaction will still be removed either by committing or by rolling back.

How do I make a table read-only in PostgreSQL

Create a trigger that throws an error:

create function do_not_change()
  returns trigger
as
$$
begin
  raise exception 'Cannot modify table procedure. 
Contact the system administrator if you want to make this change.';
end;
$$
language plpgsql;

create trigger no_change_trigger
  before insert or update or delete on "procedure"
  execute procedure do_not_change();

Note that procedure is a keyword. You should avoid a table with that name.

---

A better option is to separate the DB-user that creates tables and the user that modifies table. The creator should be the owner and the "modification" user is only granted the necessary privileges: select,insert,update,delete for all regular tables and only select for the procedure table. That will however require some changes to your deployment scripts, but is definitely the more secure option (especially if only a few people know the "creator" password)

Connect to SQL Server in Python with ReadOnly

readonly=True is an attribute of pyodbc's connect() method, not a part of the connection string you pass to SQL Server. IMHO try just passing all the attributes instead of building a full connection string:

conn = pyodbc.connect(driver='{SQL Server}', host=<server>, database=<db>,
  trusted_connection='yes', user='', password='', readonly = True)

Or, like the answer you mentioned offered, use:

conn = pyodbc.connect('driver={ODBC Driver 17 for SQL Server};'
  + 'SERVER=...;DATABASE=...;'
  + 'UID=' + user + ';PWD=' + password + ';'
  + 'ApplicationIntent=ReadOnly')

Also, are you intentionally connecting explicitly to a secondary? Why don't you connect to the AG name and if your app is doing only read operations then the worst that happens (if, say, someone breaks read-only routing) is that those read operations happen on the primary. Your code should never connect to a specific physical host / cluster node and assume that will always be a read-only secondary... what happens if there is a failover tomorrow? Who's updating all the connection strings?

---

Related Topics