Is it possible to create a custom schema in a database such that users that have read-only access in that database for the dbo schema can be set up with the ability to create and modify entities for the custom schema?
You shouldn't think about permissions in the sense that you have to own something to make changes to it. All you need to create an object in a schema is the ability to create an object in the database, and authorization on the schema (there are other ways but they are less secure). You also should think about applying the same permissions to n number of users via a role as opposed to applying those permissions individually to each user.
Here's a working example that demonstrates the syntax required for all of the things you want to do, I believe.
First, create logins from Windows, if they don't already exist:
USE [master]; GO CREATE LOGIN [Domain\Username1] FROM WINDOWS; CREATE LOGIN [Domain\Username2] FROM WINDOWS;
Add those users to the database (create this database as a test, perhaps):
USE a_database; GO CREATE USER [Domain\Username1] FROM LOGIN [Domain\Username1]; CREATE USER [Domain\Username2] FROM LOGIN [Domain\Username2];
Create a schema for them:
CREATE SCHEMA Pyramid;
Create a role, so that you don't have to maintain these permissions on every individual user, and add both users to that role:
CREATE ROLE PyramidRole; GO -- on modern versions: ALTER ROLE PyramidRole ADD MEMBER [Domain\Username1]; ALTER ROLE PyramidRole ADD MEMBER [Domain\Username2]; -- on dinosaurs: EXEC sys.sp_addrolemember @rolename = N'PyramidRole', @membername = N'[Domain\Username1]'; EXEC sys.sp_addrolemember @rolename = N'PyramidRole', @membername = N'[Domain\Username2]';
Set the permissions on the role:
-- need specific create / alter permissions: GRANT CREATE TABLE, CREATE VIEW, CREATE FUNCTION, CREATE PROCEDURE TO PyramidRole; -- to be explicit, you want them to /read/ from dbo, so: GRANT SELECT, VIEW DEFINITION ON SCHEMA::dbo TO PyramidRole; -- finally, this will allow them to create/alter objects in their schema: ALTER AUTHORIZATION ON SCHEMA::Pyramid TO PyramidRole; -- you may need other permissions depending on what else you need them to do
Now, try it out:
EXECUTE AS USER = N'[Domain\Username1]'; GO CREATE TABLE dbo.what(id int); -- Fails with Msg 2760 GO CREATE TABLE Pyramid.what(id int); -- Succeeds GO REVERT;
Now, as you add more users and you want them to inherit this same set of permissions, you just add them to the database, and then add them to the role using
ALTER ROLE ... ADD MEMBER ....
More info and more syntax examples: