question

techresearch7777777-7743 avatar image
0 Votes"
techresearch7777777-7743 asked ErlandSommarskog answered

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

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

Thanks in advance.

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered

Hi @techresearch7777777-7743,

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.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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://docs.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.)

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

techresearch7777777-7743 avatar image
0 Votes"
techresearch7777777-7743 answered

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

Thanks for the replies.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.