How to get users to see specific schemas only

Question

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.

Answer 1

As I understood above question; there is need to isolate specific database schema user access for particular SQL login.

Here in below provided sample SQL, I have created UserSchemaLogin (which is SQL Login created prior have no role assigned and no access to any DB). Then below DCL statements would grant appropriate role to the specific UserSchema and deny access across other schemas (incl. dbo schema) in the database.

USE [YourDatabase]
GO

IF EXISTS(select 1 from sys.sysusers where [name] = 'UserSchemaLogin')
    DROP USER UserSchemaLogin ;
GO
CREATE USER [UserSchemaLogin] 
    FOR LOGIN [UserSchemaLogin]
    WITH DEFAULT_SCHEMA = [UserSchema];
PRINT 'User Schema User [UserSchemaLogin] created successfully!' 
GO

ALTER AUTHORIZATION ON SCHEMA::[UserSchema] TO [dbo]
GO

GRANT ALTER ON SCHEMA::UserSchema TO UserSchemaLogin
GRANT CREATE SEQUENCE ON SCHEMA::UserSchema TO UserSchemaLogin;
GRANT DELETE ON SCHEMA::UserSchema TO UserSchemaLogin;
GRANT EXECUTE ON SCHEMA::UserSchema TO UserSchemaLogin;
GRANT INSERT ON SCHEMA::UserSchema TO UserSchemaLogin;
GRANT SELECT ON SCHEMA::UserSchema TO UserSchemaLogin;
GRANT UPDATE ON SCHEMA::UserSchema TO UserSchemaLogin;
GRANT VIEW CHANGE TRACKING ON SCHEMA::UserSchema TO UserSchemaLogin
GRANT VIEW DEFINITION ON SCHEMA::UserSchema TO UserSchemaLogin;

PRINT 'Schema User [UserSchemaLogin] has GRANTED Rights to Schema [UserSchema]' 
GO

DENY ALTER ON SCHEMA::[dbo] TO UserSchemaLogin;
DENY CREATE SEQUENCE ON SCHEMA::[dbo] TO UserSchemaLogin;
DENY DELETE ON SCHEMA::[dbo] TO UserSchemaLogin;
DENY EXECUTE ON SCHEMA::[dbo] TO UserSchemaLogin;
DENY INSERT ON SCHEMA::[dbo] TO UserSchemaLogin;
DENY SELECT ON SCHEMA::[dbo] TO UserSchemaLogin;
DENY UPDATE ON SCHEMA::[dbo] TO UserSchemaLogin;
DENY VIEW CHANGE TRACKING ON SCHEMA::[dbo] TO UserSchemaLogin;
DENY VIEW DEFINITION ON SCHEMA::[dbo] TO UserSchemaLogin;

PRINT 'Schema User [UserSchemaLogin] has DENY Rights to Schema [dbo]' 
GO