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

Question

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, sp, view index, index maintenance, functions, UDF and udts. every thing in programabilty and querying permission viewing permision for system views like for information schema, and sys schema.

Yours sincerely.

Answer 1

You already listed in your post all required permissions in detail, so grant them; what's the detailed problem here?

Answer 2

Hi rajesh yadav

Transact SQL (like all SQL extenuation languages) is based on English which is why if you want to do something then usually by asking the question you provide the solution.

For example, you asked to grant permission to create table for the user X, So the query is

GRANT CREATE TABLE TO MelanieK  

This is the almost the same with all your requests. You simply need to GRANT the permission which you asked for to the USER.

For more detailed information about the optional permission in the database level, which you can GRANT, check the following document:

https://learn.microsoft.com/en-us/sql/t-sql/statements/grant-database-permissions-transact-sql?view=sql-server-ver16&WT.mc_id=DP-MVP-5001699

On the left you can also see the links to documents for other types of permission (other levels like for specific object for example)

But in most cases, the query is (almost) simply the same as the English request

Hope that this clovers your needs :-)

Answer 3

Hi @rajesh yadav ,

It should be based on your needs, just like these that you propose to grant permissions.
The minimum privilege is probably that you grant no privileges other than the ones you want
https://dba.stackexchange.com/questions/183792/what-minimum-permissions-and-or-roles-must-a-sql-server-login-possess-to-be-able

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.