How permission on column works?

Question

question

Farhangm asked Oct 21, '15

How permission on column works?

I created a table with 4 columns. I denied access to last column for a user named XYZ. Then i added 5th column. User can not access the 5th column as well. Why? If i as sysadmin use the command execute as user=XYZ i can access the 5th column. Why?

permissions columns

7

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tom Staab ♦ commented · Oct 21, 2015 at 01:25 PM

This is more of a comment than a true answer, but I wanted to use the answer to better format the SQL. You can use this to confirm on which columns the user has any permissions. If the user has SQL access, have him/her execute this statement: SELECT * FROM sys.fn_my_permissions('MyTable', 'object'); It will return a row for each column the user has some permission to access. You could also test this yourself by wrapping like this: EXECUTE AS USER='whomever'; SELECT * FROM sys.fn_my_permissions('MyTable', 'object'); REVERT;

1 ·

Magnus Ahlkvist commented · Oct 21, 2015 at 05:27 AM

In which way can't the user access the fifth column? SELECT col1, col2, col3, col5 FROM table Some other way?

0 ·

Farhangm commented · Oct 21, 2015 at 10:51 PM

@Magnus Ahlkvist when i execute SELECT col1, col2, col3, col5 FROM table as user i get select permission denied on col5

0 ·

Farhangm commented · Oct 22, 2015 at 12:15 AM

@Tom Staab SELECT * FROM sys.fn_my_permissions('MyTable', 'object');/ col1 SELECT/ col2 SELECT/ col3 SELECT/ EXECUTE AS USER='whomever'; SELECT * FROM sys.fn_my_permissions('MyTable', 'object'); REVERT;/ col1 SELECT/ col2 SELECT/ col3 SELECT/ col5 SELECT/ ps: This result is based on sql 2012. In 2014 both results are the same

0 ·

Farhangm commented · Oct 22, 2015 at 01:26 AM

@Tom Staab The data type is varchar(10) and to clarify: I recreated the table in sql server 2014 and tested the result.. so it does not seem to be a client issue

0 ·

Show more comments

Answer 1 · 2015-10-22T00:44:14Z

Tom Staab answered Oct 22, '15

Now that we have a little more information, I have a theory for the possible cause. It's based on your comment that the results are different when you use a different client version. What is the data type of the new column? I remember a problem years ago when people were using a 2005 client and trying to access a table that included a column of type datetime2. Could this be a data type issue rather than a permission issue? Is it a CLR data type? Of course, if you tell me the new column is just a basic type like int or varchar(10), then I'm back to the drawing board for ideas.

9

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Farhangm commented · Oct 22, 2015 at 01:19 AM

@Tom Staab The data type is varchar(10) and to clarify: I recreated the table in sql server 2014 and tested the result.. so it does not seem to be a client issue

0 ·

Tom Staab ♦ commented · Oct 22, 2015 at 01:29 AM

Can you please post the GRANT and DENY statements you used? Please also include any inherited permissions from roles or possibly from Windows groups.

0 ·

Farhangm commented · Oct 22, 2015 at 01:40 AM

create user [whomever] for login [whomever] alter role [db_datareader] add member [whomever] deny select on [dbo].[mytable]([col4]) to [whomever]

0 ·

Tom Staab ♦ commented · Oct 22, 2015 at 04:20 PM

I don't think this is it, but can you please try it anyway? REVOKE SELECT ON dbo.mytable(col5) TO [whomever]; That will remove any explicit permissions (grant or deny) on that column for that user.

0 ·

Tom Staab ♦ commented · Oct 22, 2015 at 04:21 PM

Here's another crazy thought: Any chance adding that 5th column happened in an uncommitted transaction?

0 ·

Show more comments

question