0

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.

1 Answer 1

0

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
3
  • But this would still mean other users will have access to the schema, since they are part of public
    – SSR
    Commented Dec 20, 2022 at 13:21
  • Its depends what role/rights assigned to other users/login. If other user/login does not allow to Schema1 but only Schema2, then above SQL need to be replace with schema2 for User2 Commented Dec 20, 2022 at 14:34
  • Other users have access to all schemas. If I grant permissions for user1 to user1_schema, other users can still access it unless I deny each of their access at individual level. How do I avoid doing it for each user
    – SSR
    Commented Dec 20, 2022 at 15:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.