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

rajesh yadav 166 Reputation points
2022-06-24T05:54:12.507+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,321 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Olaf Helper 39,181 Reputation points
    2022-06-24T06:00:06.297+00:00

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

    0 comments No comments

  2. Ronen Ariely 15,081 Reputation points
    2022-06-24T06:19:35.003+00:00

    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)

    214579-image.png

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

    Hope that this clovers your needs :-)

    0 comments No comments

  3. YufeiShao-msft 7,046 Reputation points
    2022-06-27T08:23:45.067+00:00

    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.