what minimum permission i should give to new login , new user and new schema.

Question

Post reply

what minimum permission i should give to new login , new user and new schema.

rajemessage 14195

Hall of Fame

Points: 3315
More actions
June 24, 2022 at 6:05 am

#4054172

Hi,
I have made a new login, new user (as I have associated my database to login) and new schema.
I need to know what minimum permission I should give to my new login, new user and new schema, so that the person should be able to create tables drop table and perform crud operations on tables, and every thing in programabilty section like sp, views, triggers , udt and functions etc etc. and querying permission viewing permission for system views like for information schema, and sys schema.
Yours sincere
June 28, 2022 at 5:39 am

This was removed by the editor as SPAM

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

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

Ken McKelvey SSCoach Points: 18842 More actions · Answer 1

https://docs.microsoft.com/en-us/sql/relational-databases/security/permissions-database-engine?view=sql-server-ver16

https://blog.netwrix.com/2020/10/16/guide-to-sql-server-permissions/

rajemessage 14195 Hall of Fame Points: 3315 More actions · Answer 2

that i will read, but can i get a rough idea.

like first login i created has public server role , and database role i have given in db_creator as i have to do CRUD operation including drop and create tables.

q1) will these roles be minimum (as i want to give this login to a perso who should only be able to create tables and do crude operations and should be able to manuplulate programability section also in a new schema which i have assigned to him.) so question is related to roles i have given is minimum or can i do some thing more.

pls give me one example ( a rough one) for the above need.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

rajemessage 14195 wrote:

that i will read, but can i get a rough idea.
...
pls give me one example ( a rough one) for the above need.

It's a funny thing... I've been working with SQL Server for more than two decades and have never had to do such a thing just because I've never worked in a shop that required such a thing. I'd have to go back and read the MS documentation and try to hammer it out, as well

The problem with such documentation is that they tell you how to build the proverbial car when all you want to know is how to check the pressure on the tires.

With that, I'll ask with and for the OP, does anyone know this off the top of their head or have a link that focuses just on this requirement (which also seems to be elusive at first blush) so that the OP can get a quick answer?

--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)

ScottPletcher SSC Guru Points: 101098 More actions · Answer 4

I've been a DBA on SQL Server for 20+ years and only for certain vendors have I ever had to go thru permission by permission. I have to admit, for developers (in qa and prod) and power users (in qa only), we just give them roles: db_ddladmin; db_datareader; and , if appropriate, db_datawriter. Add whatever specific DENYies are needed, and whatever GRANT VIEWs are needed.

For many places, it's just not practical to maintain a list of very specific permissions for very specific company roles. Yeah, that's "best practice", but only if you've got the people resources to maintain it all.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

Like I said, I've never had to do such a thing as that requested but... I believe that the user of a schema can create objects and that's about it. You'll have to give explicit permissions to drop objects in that schema and explicit permissions to do SELECT, INSERT, UPDATE, and DELETE. Might have to grant EXEC privs but I just don't know. But, read on... there's some hope below...

Here's a link for how to grant some of the privs for a schema. I'd scroll down to the "Remarks" section. They have a list of schema level privs. It looks like "Control" (MAKE SURE IT'S ONLY GIVEN AT THE SCHEMA LEVEL) might be what you're looking for to give the owner of the schema "owner level privs at the schema level" in a single command. Obviously, when you look at the privs chart in the "Remarks" section, you'll see the only exception. With that, I'll also warn, make sure you read the "caution" in the section with the Light Red (almost Pink) background about self-elevation of privs. That suggests to me that "CONTROL" may not be the right priv to give.

Here's the link for the article (MS documentation).

https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-schema-permissions-transact-sql

--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)

jonau1 Grasshopper Points: 19 More actions · Answer 6

You could start with this

alter user <usename> default_schema = <SchemaName>

grant control on schema::<SchemaName>

grant view definition to <username>

grant create table to <username>

grant create view to <username>

grant create default to <username>

grant create procedure to <username>

grant create assembly to <username>

grant create aggregate to <username>

This reply was modified 2 years, 4 months ago by jonau1.
This reply was modified 2 years, 4 months ago by jonau1.

Steve Jones - SSC Editor SSC Guru Points: 736198 More actions · Answer 7

I'd do what jonau1 has suggested above. Allow the user CONTROL on the schema, then grant the various rights to the user to work with objects.