question

Farhangm avatar image
Farhangm asked

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?
permissionscolumns
7 comments
10 |1200

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

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 Like 1 ·
In which way can't the user access the fifth column? SELECT col1, col2, col3, col5 FROM table Some other way?
0 Likes 0 ·
@Magnus Ahlkvist when i execute SELECT col1, col2, col3, col5 FROM table as user i get select permission denied on col5
0 Likes 0 ·
@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 Likes 0 ·
@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 Likes 0 ·
Show more comments

1 Answer

·
Tom Staab avatar image
Tom Staab answered
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 comments
10 |1200

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

@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 Likes 0 ·
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 Likes 0 ·
create user [whomever] for login [whomever] alter role [db_datareader] add member [whomever] deny select on [dbo].[mytable]([col4]) to [whomever]
0 Likes 0 ·
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 Likes 0 ·
Here's another crazy thought: Any chance adding that 5th column happened in an uncommitted transaction?
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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