Jazz Jazz - 16 days ago 8
SQL Question

GRANT Permissions seem not to work on a VIEW

I have the following view (called

view3
)

enter image description here

I created a role and granted it
SELECT
and
UPDATE
rights just on two columns.

CREATE ROLE Testrole
GRANT SELECT (Doc_ID, [Total Attentions]) ON view3 TO Testrole


Then I assigned the role to a user (
test
) already created

ALTER ROLE Testrole ADD MEMBER test


But when the transaction checking if all is ok is executed, all the columns are shown instead of the two required (the same image above).

This is the script

CREATE LOGIN logtest
WITH PASSWORD = 'logtest'

CREATE USER test
FOR LOGIN logtest

CREATE ROLE Testrole
GRANT SELECT (Doc_ID, [Total Attentions]) ON view3 TO Testrole
ALTER ROLE Testrole ADD MEMBER test


SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

BEGIN TRANSACTION
EXECUTE AS USER = 'test'

SELECT * /*This should give an error*/
FROM view3

SELECT Doc_ID, [Total Attentions] /*This should work just fine*/
FROM view3

REVERT

ROLLBACK

Answer

GRANT permissions are cumulative. These symptoms suggest that view-level (all columns) permissions exist, inherited from this or other roles. Run the query below to see if this is the case.

SELECT
      permission_name
    , OBJECT_NAME(major_id) AS ObjectName
    , CASE WHEN c.name IS NULL THEN 'All Columns' ELSE c.name END AS ColumnName
    , USER_NAME(grantee_principal_id) AS Gratee
FROM sys.database_permissions AS p
LEFT JOIN sys.columns AS c ON 
    c.object_id = p.major_id
    AND c.column_id = p.minor_id
WHERE
    major_id = OBJECT_ID(N'view3');