Procedure returns incorrect result when permissions are not granted
-
I have three users: admin, user2 and user3. User admin is db_owner while user2 and user3 are regular users. I have table dbo.T2 (Id, Value1, Value2). For user2 I granted select on Id and Value1 columns, while user3 isn't granted select on this table.
For my Access database it's required to determine which columns (in defined table) users don't have granted permissions to select. It's required so that I could hide controls on the form so that user wouldn't see and use them.
Here's the solution I have:
create procedure dbo.GetDeniedColumns
@user varchar(50), @table varchar(100)
as
begin
set nocount on;
declare @table_id int = object_id('dbo.' + @table);
select c.name
from sys.columns c
left outer join sys.database_permissions p
on p.class = 1
and p.major_id = c.object_id
and p.minor_id = c.column_id
and p.state = 'G'
and p.grantee_principal_id = DATABASE_PRINCIPAL_ID(@user)
where c.object_id = @table_id and p.state is null;
end;
This code works perfectly under admin and user2, but... not under user3! Here's the query:
execute as user = 'user3';
execute dbo.GetDeniedColumns @user = 'user3', @table = 'T2';
The result is empty. The result should be three columns (Id, Value1 and Value2) since I didn't grant selection on these columns. But as soon as I grant select permission on at least one column of T2 table (say, Id), the procedure returns correct result! Can anyone explain this behavior?
P.S. The problem IS NOT with "execute as", because I tried it out under the user3 himself - the result is still empty.
-
"The visibility of the metadata in catalog views is limited to securables that a user either owns or on which the user has been granted some permission. For more information, see Metadata Visibility Configuration."
https://msdn.microsoft.com/en-us/library/ms176106.aspx
Since the user doesn't have access their account is unable to see it in the catalogue views.
-
It could assist in preserving you claiming that a high intake of wholesome MMA muscle Max Gain Xtreme constructing exercising dietary supplements can be useful within the control of positive ailments disorders which commonly manifest as human beings become older. Max Gain Xtreme[/url] if you for you and your circle of relatives, you could add those MMA muscle building workout supplements to your meals instruction and make sure that you'll get enough amounts whenever. http://boostupmuscles.com/max-gain-xtreme-review/
[/url]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply