Permission related question

Question

Post reply

Permission related question

LearningDBA

SSCertifiable

Points: 6593
More actions
March 22, 2024 at 4:23 pm

#4400191
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?
- This topic was modified 1 month ago by LearningDBA.
"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]

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80320 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

LearningDBA SSCertifiable Points: 6593 More actions · Answer 2

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]

Ken McKelvey SSCoach Points: 18768 More actions · Answer 3

DDL? Test Data? You have enough points to know this.

LearningDBA SSCertifiable Points: 6593 More actions · Answer 4

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]

Ken McKelvey SSCoach Points: 18768 More actions · Answer 5

RTFI

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.

This reply was modified 1 month ago by Ken McKelvey.

lkennedy76 SSChampion Points: 13683 More actions · Answer 6

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