Grant user to create schemas

Question

Grant user to create schemas

Siten0308

SSCarpal Tunnel

Points: 4404
More actions
March 30, 2020 at 10:32 pm
Go to Answer
#3739125

Hello,
is there a better solution to give a user or user group permission to create schema's instead of just giving them db_accessadmin?
thanks in advance
Phil Parkin

SSC Guru

Points: 246912
More actions
March 30, 2020 at 11:34 pm
Answer
#3739133

Siten0308 wrote:
Hello,
is there a better solution to give a user or user group permission to create schema's instead of just giving them db_accessadmin?
thanks in advance
I assume that you've Googled this?
Users can create schemas in a database if you grant them CREATE SCHEMA permission.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Siten0308 SSCarpal Tunnel Points: 4404 More actions · Answer 1

ahh thanks, i was confused as when i think of create schema... i think its a command to Create a schema, such as create schema "Name of Schema".

but if it gives the user access to create schema with this command for a database, then cool. thanks.

Jeff Moden SSC Guru Points: 1004250 More actions · Answer 2

The next question would be.... why are you giving users privs to create schemas?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Siten0308 SSCarpal Tunnel Points: 4404 More actions · Answer 3

Hey Jeff,

sadly not my choice, but they are deploying an application, and the application is requesting to create some schemas, so, I hoped they would give me the scripts for me to create the objects but, its all in an executable that needs to be run... app team... 🙁

Jeff Moden SSC Guru Points: 1004250 More actions · Answer 4

Siten0308 wrote:

Hey Jeff,
sadly not my choice, but they are deploying an application, and the application is requesting to create some schemas, so, I hoped they would give me the scripts for me to create the objects but, its all in an executable that needs to be run... app team... 🙁

Ah... got. I believe I'd setup a "service account" that has the privs and then have the app use that service account, rather than the users.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Siten0308 SSCarpal Tunnel Points: 4404 More actions · Answer 5

great thank you Jeff for the additional input. that makes sense. The App team will use that account for all app to interact from app server to database. thanks again.

Jeff Moden SSC Guru Points: 1004250 More actions · Answer 6

I recommend that f you have more than one app, use more than one service login. Each app should have it's own so you can more easily trace things like what the cause of blocking and other issues are much more easily.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)