0

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?

  • I think I've figured out how to make a User the owner of a Schema and then grant them the appropriate permissions to create entities but I'm still lacking a conceptual understanding of the fundamentals. For example, what if I wanted to provide multiple Users the ability to create entities in the same specific schema. Can I make multiple Users the owner or is the proper way to do this is via a Windows Group Login or is there a better SQL equivalent to accomplish this? – J.D. Sep 10 '19 at 22:01
8

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:

|improve this answer|||||
  • Thanks Aaron, top notch as per usual. One question though, since to accomplish what I'm trying to do requires the role to have the ALTER privilege at the database level, does that mean when a new schema is created in that database, the role will automatically have the ALTER privilege on that new schema? – J.D. Sep 11 '19 at 13:45
  • Also, anyone like me who is still riding the dinosaur of SQL Server 2008 R2 (or older), ALTER ROLE RoleName ADD MEMBER [Domain\Username] doesn't work (throws a syntax error) and instead you need to use: EXECUTE sp_addrolemember RoleName, 'Domain\Username'; -- This procedure could be deprecated in future versions of SQL – J.D. Sep 11 '19 at 13:57
  • 1
    And yeah, sorry, it was written for supported versions of SQL Server, I didn't notice your dinosaur tag. :-) – Aaron Bertrand Sep 11 '19 at 18:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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