March 22, 2024 at 4:23 pm
I have a view in a database A which is created from a table in database B. This view has PII data and I want to restrict users from running select against it. So I ended up running Deny select against all users except CI user. CI user now has DB Owner rights but when he runs the select against the restricted view, he gets 'Select permission denied' error. When I grant that user with SA rights, it works. DB Owner should have worked correct? Not sure what I am missing. Any idea how I can tackle this issue?
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
March 23, 2024 at 5:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
March 25, 2024 at 6:31 pm
Any feedback?? Anyone??
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
March 26, 2024 at 1:27 pm
DDL? Test Data? You have enough points to know this.
March 26, 2024 at 3:24 pm
I will just provide some details.
As of last week, almost everyone had access to run select against some restricted views
Then I generated the list of users who had read only and ran this command
DENY SELECT ON dbo.my_view TO users;
After that, we had handful of users tested the select and they couldn't see the view.
And then I ended up granting one specific AD group with owner rights on DBs (where the view is and where the underlying table is) but when user runs a select against a view, he gets "select permission was denied on the object" error. It works when I grant with SA rights but as soon as sa right is revoked then error occurs. I thought DB owner would allow users to see any object in a database, run select, perform DML, exec procs etc... but it is not working.
Even after user with DB owner rights, I explicitly ran this command to make sure permissions were messed up. But no luck
GRANT SELECT ON dbo.my_view TO the special user;
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
March 26, 2024 at 3:46 pm
Denies a permission to a principal. Prevents that principal from inheriting the permission through its group or role memberships. DENY takes precedence over all permissions, except that DENY does not apply to object owners or members of the sysadmin fixed server role. Security Note Members of the sysadmin fixed server role and object owners cannot be denied permissions."
Laziness is the only reason I can think of for why you would even consider using DENY here.
March 27, 2024 at 7:21 pm
check the permissions on the view, you basically denied all for select permissions for that view. To test that out run grant select for the one user with DBO perms, if they can run it after that you have your answer.
MCSE SQL Server 2012\2014\2016
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply