views:

131

answers:

3

What's the best way to determine if a given SQL Server table is readonly, either because of account permissions or the database being marked as read-only?

A: 

You mean besides trying to write to it right?

Joshua
A: 

Have a rummage around in SQL Server's Information Schema Views, specifically, TABLE_PRIVILEGES.

Marcelo Cantos
+1  A: 
--for database
SELECT DATABASEPROPERTYEX(DB_NAME(), 'IsReadOnly')

--for tables.
SELECT
   *
FROM
   sys.tables t
   LEFT JOIN
   sys.database_permissions dp2 ON dp2.major_id = t.object_id AND dp2.permission_name = 'SELECT'
WHERE
   NOT EXISTS (SELECT * FROM 
         sys.database_permissions dp
      WHERE
         dp.major_id = t.object_id
         AND
         dp.permission_name IN ('INSERT', 'DELETE', 'UPDATE'))

Modify * as needed to get what you want: this checks for none/SELECT where there is no I/U/D rights

You also have FILEGROUPPROPERTY etc if you have read only filegroups

Edit: for current connection and direct table rights per user. Does not iterate through group hierarchy

SELECT
   *
FROM
   sys.tables t
   JOIN
   sys.database_permissions dp2 ON dp2.major_id = t.object_id AND dp2.permission_name = 'SELECT'
WHERE
   dp2.grantee_principal_id = USER_ID()
   AND
   dp.permission_name IN ('INSERT', 'DELETE', 'UPDATE'))
gbn
I connected to a DB with an account that belonged to the public and db_datareader roles. Confirmed it had read-only access, and neither of these queries indicated that I had read-only access, from what I could tell.
Rono
in that sense, you can't detect for the connection if it's read only. The 2nd query I gave will tell you that public and db_datareader do not have I/U/D permissions which is the same.
gbn
What columns should I look at to determine that? I ran the second query on a read-only connection and an editable connection and I did not see a useful difference between them. Or are you saying this is not possible?
Rono
@rono: correct. you can only detect permissions. i'll change the query though so it works for the current connection: should have thought of that earlier
gbn
Sorry, but looking at the raw data in sys.database_permissions using my read-only connection, there's nothing I see in there saying anything is read-only or I'm denied permissions on something.
Rono
@rono: it's the lack of UPDATE, INSERT or DELETE rights you're looking at. There is ***no*** flag that says "read only"
gbn
If I connect to the database with a account with edit rights and view this table, I do not see any records where permission_name is INSERT, DELETE or UPDATE. I just see CONNECT, SELECT and EXECUTE. It seems that determine what roles the current user is in would do the trick.
Rono