1

I have a database that will be shared across multiple related types of applications for different users. I want to be able to restrict each database user to essentially have full control over their own objects, with only read access to the other objects of the database that they didn't create.

Is this possible and what's the best way to accomplish this?

1 Answer 1

5

Look into using Schemas. Each User can have (and own) their own Schema for their objects that they have full control over. Then you can grant SELECT access for them to other Schemas.

When doing this, you need to make sure that both of the following conditions are met:

  1. Each Schema "owner" (i.e. AUTHORIZATION) is for the intended User
  2. Each User has their default Schema set to their Schema

As the schema owner, each user will have CONTROL permission over the schema and all objects within. SELECT permission can then be granted on other schemas as needed:

CREATE SCHEMA User1 AUTHORIZATION User1 ;
GO
CREATE SCHEMA User2 AUTHORIZATION User2;
GO
CREATE SCHEMA User3 AUTHORIZATION User3;
GO
ALTER USER User1 WITH DEFAULT_SCHEMA = User1;
ALTER USER User2 WITH DEFAULT_SCHEMA = User2;
ALTER USER User3 WITH DEFAULT_SCHEMA = User3;
GO
GRANT SELECT ON SCHEMA::User2 TO User1;
GRANT SELECT ON SCHEMA::User3 TO User1;
GRANT SELECT ON SCHEMA::User1 TO User2;
GRANT SELECT ON SCHEMA::User3 TO User2;
GRANT SELECT ON SCHEMA::User1 TO User3;
GRANT SELECT ON SCHEMA::User2 TO User3;
GO

Please see:

1
  • @DanGuzman not sure if you will get a notification for this, but mucho thanks for adding the example code here. I just ran out of time (and steam) that day ;-). And thanks for the typo fix on the allocation_unit question. Again, the help is appreciated :-). Commented May 8, 2018 at 14:43

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.