Sql-server – Object Level Security

loginsroleSecuritysql serverusers

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?

Best Answer

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:

Related Question