Thanks for your time.
I need to have set of schemas which are to be viewed by single users or a group of users ( set in a role) and others should not have access to them. I don’t want to put each user to their department roles and then deny access to them as I give access to that department role. Can I change anything in public role to achieve this.
When I deny select in public for a particular schema, even the people who are granted the access can’t do select. Grant is taken over by Deny.using Mssqlserver.
Any help is appreciated
Edit: I’ll provide an example
Assume user1, user2, userA, userB Schemas user1_schema, schema1, schema2
I want user1 only to be able to see user1_schema! I am aware if I deny select, etc to other other users on this schema, that would suffice. But, if I have 150 users, I dnt want to go on add them to each schema and deny access or when I have to add a user, go through several schemas to deny permissions at each schema level.
Initially I imagined if I deny access to public role, and grant access to a specific user/role along with it, it would work by denying access to everyone but let particular user/role have access. But i realised Deny trumps Grant and no one can access the schema.