locked
Permission for a user just to create indexes on table RRS feed

  • Question

  • I'm looking for a way to give permission for a user just to create Index on a table and deny everything else like alter column ,change schema on a table ,Drop table etc. 

    SQL Server version : 2017  
    Tuesday, June 2, 2020 7:59 PM

Answers

  • There are solutions to your problem, although I share the concerns of the other posters. If the users want to be able speed up their analysis, what if they want to add extra columns to the table that could speed up their analysis, for instance running susm?

    One solution is to give them ALTER permissions on the table, and then you set up a DDL trigger, which will roll back everything which is not a CREATE_INDEX operation on these tables, if the user is in a certain role. Beware that if they get the idea to alter a column in a way that requires the table to be rebuilt under the covers, the entire operation will be perform and then rolled back at the end when the DDL trigger fires. So this solution does not stop them at the gate.

    The other solution is to give them a stored procedure which accepts a table name, a columns list and other index options. Then you build the CREATE INDEX statement inside the procedure. Then you create a certificate and sign this procedure. You create a user from the certificate and grant this user ALTER on the tables in question. This is a technique that I describe in a whole lot more detail in an article on my web site: http://www.sommarskog.se/grantperm.html

    Beware that with this solution you will need to check their input so that they don't cheat on your and inject other commands into the procedure.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, June 2, 2020 9:38 PM

All replies

  • I'm looking for a way to give permission for a user just to create Index on a table and deny everything else like alter column ,change schema on a table ,Drop table etc. 

    SQL Server version : 2017  

    Good day Jay,

    Starting from the second part

    >> and deny everything else

    By default, new users which you create, has no permission so there is no reason to DENY anything

    >> give permission for a user just to create Index on a table

    INDEX are created when you create or alter the object which they indexes Like a table or a view. If a user can create the table then he can can create the index as part of the procedure of creating the table (it can be done in the same query) or after the table exists. Columns like index are part of the definition of the table. Therefore, how can you be able to do one and not the pother?!?

    To create index the user requires ALTER permission on the table or view.


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Tuesday, June 2, 2020 8:13 PM
  • Thank you Ronen. 

    To elaborate my question

    DBA creates the table and given it to the user.

    User will have only read only permissions to the table. But users requesting to create their own Indexes for those tables. 

    If we give Alter permission to the user they will be able to modify the table schema too right? I don't want that to happen is there any way around it ? 

    Tuesday, June 2, 2020 8:25 PM
  • No, there is no way to allow users to create indexes without allowing them to alter the table also.

    "Users" should not be creating indexes.  What is the purpose in allowing users to create indexes, instead of the DBA?

    Tuesday, June 2, 2020 8:28 PM
  • Thanks Tom.

    That's for raw/uncleaned data loaded to tables for user to analyse, how data looks like . DBA don't want to spend time on raw/ uncleaned data and get the indexes created. 

    Tuesday, June 2, 2020 8:36 PM
  • Thank you Ronen. 

    To elaborate my question

    DBA creates the table and given it to the user.

    User will have only read only permissions to the table. But users requesting to create their own Indexes for those tables. 

    If we give Alter permission to the user they will be able to modify the table schema too right? I don't want that to happen is there any way around it ? 

    Hi,

    >> User will have only read only permissions to the table. But users requesting to create their own Indexes for those tables. 

    This make no sense. INDEXes have a huge impact on the system and you should not let your users to do it even if was allowed! Each index which a user create might reduce the performance on all other users and each insert/update on the table for example. Indexes are not free!

    >> If we give Alter permission to the user they will be able to modify the table schema too right?

    No. You can give permission on table or view and no need to give on the entire schema. The user can only control the table or view.

    >> I don't want that to happen is there any way around it ? 

    Yes, there is always a workaroun. The question should never be "is there a way..." but "what is the way..." 😀

    You can create for example an application which connect the server with specific user which has the minimum privileges that is needed and let users only work throw the application. Do not let users to create indexes directly and do not create indexes without understanding the impact


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    Tuesday, June 2, 2020 9:24 PM
  • There are solutions to your problem, although I share the concerns of the other posters. If the users want to be able speed up their analysis, what if they want to add extra columns to the table that could speed up their analysis, for instance running susm?

    One solution is to give them ALTER permissions on the table, and then you set up a DDL trigger, which will roll back everything which is not a CREATE_INDEX operation on these tables, if the user is in a certain role. Beware that if they get the idea to alter a column in a way that requires the table to be rebuilt under the covers, the entire operation will be perform and then rolled back at the end when the DDL trigger fires. So this solution does not stop them at the gate.

    The other solution is to give them a stored procedure which accepts a table name, a columns list and other index options. Then you build the CREATE INDEX statement inside the procedure. Then you create a certificate and sign this procedure. You create a user from the certificate and grant this user ALTER on the tables in question. This is a technique that I describe in a whole lot more detail in an article on my web site: http://www.sommarskog.se/grantperm.html

    Beware that with this solution you will need to check their input so that they don't cheat on your and inject other commands into the procedure.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, June 2, 2020 9:38 PM
  • Hi Jay.Y,

    According to MS document, creating index on a table or view requires ALTER permission on the table or view. User must be a member of the sysadmin fixed server role or the db_ddladmin or db_owner fixed database roles.

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, June 3, 2020 6:22 AM
  • For that purpose I would suggest you just put it somewhere they can control, special database or their local database server and give them owner rights.  Or export it to something like Excel to look at, instead of using SQL Server at all.
    Wednesday, June 3, 2020 1:33 PM