确定 SQL Server 表是否为只读

确定给定 SQL Server 表是否为只读（由于帐户权限或数据库被标记为只读）的最佳方法是什么？

--for database (updated for newer versions)
SELECT DATABASEPROPERTYEX(DB_NAME(), 'Updateability')

--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'))

根据需要修改 * 以获得您想要的内容：这将检查没有 I/U/D 权限的 none/SELECT

你还有文件组属性 http://msdn.microsoft.com/en-us/library/ms188069.aspx等等如果您只读文件组

编辑：针对每个用户的当前连接和直接表权限。不遍历组层次结构

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'))