Unable to restrict permission

Question

Post reply

Unable to restrict permission

LearningDBA

SSCertifiable

Points: 6621
More actions
June 9, 2026 at 2:14 pm

#4862547

I have 13 restricted views in my EDW DB.
6 of them are created from DB1
7 of them are created from DB2.
Only 1 group should have access to view the data. Rest of the logins shouldn't. No AD group has db_datareader in any of the database. All 3 DBs reside on the same server.
Logins have been granted with grant select on all unrestricted views on all DB1, DB2 and EDW but some of the user who are not part of restricted group are still able to view in EDW. Any leads on where to look for is highly appreciated
"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 4 posts - 1 through 4 (of 4 total)

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

Site Owners SSC Guru Points: 80223 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.

Steve Jones - SSC Editor SSC Guru Points: 742441 More actions · Answer 2

Have you queried fn_my_permissions for the user?

https://learn.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-my-permissions-transact-sql?view=sql-server-ver17

LearningDBA SSCertifiable Points: 6621 More actions · Answer 3

It is not making sense to me.

I have this role 'READ_ONLY'. It has read only access to 550 non PII views out of 570.

When I run this, I get 550 records but when I run "like '%restricted%'", I get 0 record which means that this read_only role does not have select permission on PII views.

SELECT 
    dp.name AS [Group_Or_Role_Name],
    dp.type_desc AS [Principal_Type],
    o.name AS [View_Name],
    p.permission_name AS [Permission],
    p.state_desc AS [Permission_State]
FROM sys.database_permissions AS p
INNER JOIN sys.database_principals AS dp 
    ON p.grantee_principal_id = dp.principal_id
INNER JOIN sys.views AS o 
    ON p.major_id = o.object_id
WHERE o.name not like '%restricted%'
and dp.name = 'READ_ONLY'

So then I run this command

ALTER ROLE Read_Only ADD member MYADGroup

Now the user is able to see PII data. HOW?

But when I run this,

ALTER ROLE Read_Only DROP member MYADGroup

user gets select denied permission for all views.

Any insight is highly appreciated.

"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]