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:
Have a rummage around in SQL Server's Information Schema Views, specifically, TABLE_PRIVILEGES.
Marcelo Cantos
2010-04-05 02:30:51
+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
2010-04-05 10:08:42
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
2010-04-08 02:34:17
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
2010-04-08 04:23:02
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
2010-04-18 02:34:34
@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
2010-04-18 08:45:13
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
2010-04-27 02:52:07
@rono: it's the lack of UPDATE, INSERT or DELETE rights you're looking at. There is ***no*** flag that says "read only"
gbn
2010-04-27 18:40:18
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
2010-05-02 03:09:50