Permissions to create schemas and grant access to specific DB User?

Question

Hello, what are the permissions-role and syntax necessary to create schemas and grant access to a specific DB user?

Thanks in advance.

Answer 1

Hi @techresearch7777777 ,

>Permissions for creating schemas;

Requires CREATE SCHEMA permission on the database.

To create an object specified within the CREATE SCHEMA statement, the user must have the corresponding CREATE permission.

To specify another user as the owner of the schema being created, the caller must have IMPERSONATE permission on that user. If a database role is specified as the owner, the caller must have one of the following: membership in the role or ALTER permission on the role.

>Syntax necessary to create schemas and grant access to a specific DB user;

The following example creates schema Sprockets owned by Annik that contains table NineProngs. The statement grants SELECT to Mandar and denies SELECT to Prasanna.

USE AdventureWorks2012;    
GO    
CREATE SCHEMA Sprockets AUTHORIZATION Annik    
    CREATE TABLE NineProngs (source int, cost int, partnumber int)    
    GRANT SELECT ON SCHEMA::Sprockets TO Mandar    
    DENY SELECT ON SCHEMA::Sprockets TO Prasanna;    
GO  

Please refer to MS document CREATE SCHEMA (Transact-SQL).

Syntax for creating DB user, refer to MS document Create a Database User.

---

If the response is helpful, please click "Accept Answer" and upvote it, thank you.

Answer 2

So I did this. I went to http://www.google.com. There I entered CREATE SCHEMA in the search box. Luckily, the first was from the T-SQL Reference, https://learn.microsoft.com/en-us/sql/t-sql/statements/create-schema-transact-sql?view=sql-server-ver15. I opened this page. Since I know that the required permissions are listed just before the examples, I went to the end of the page and scrolled upwards. But that is just by habit. There is also a table of contents on top.

So there you find the answer to that question.

I leave it as an exercise for you to find the answer for the second part. (I mean, since I don't know the answer by heart, it's either you or I who have look it up.)

Answer 3

Sorry I kind of was more curious what permissions like which DB role is needed if at that level.

Thanks for the replies.

Answer 4

Sorry I kind of was more curious what permissions like which DB role is needed if at that level.

Most topics have roles listed in the Permissions section, but there isn't one for CREATE SCHEMA. I did a quick test, and it seems that db_ddladmin is sufficient. Obviously, db_owner will also work.

For creating user, I guess you are looking for db_securityadmin, but I did not test, nor check the documentation.