Post reply

Allow only CREATE INDEX on certain tables.

  • March 26, 2004 at 11:31 am

    #84544

    Hey!

    I'm pretty new to security & user permission issues.

    Anyway, is there any way in SQL 2000 to grant a user just CREATE INDEX permission?

    Thanks!

     

    Kindest Regards,

  • March 26, 2004 at 11:42 am

    #500717

    I think not. Only the table owner or db owner.

    In the other hand, you could create a stored procedure wich creates an index and then you grant execute permission on that procedure to the users that you want.

  • March 26, 2004 at 1:10 pm

    #500732

    Well that kind of sucks!

    Thanks for the info !

    Paul

    Kindest Regards,

  • March 26, 2004 at 1:22 pm

    #500734

    ANy ROLE that grants the create index permission will have it and ou cannot stop them unless you deny but that is as a whole not for a single object.

  • March 26, 2004 at 5:33 pm

    #500776

    Hey!

    OK... I need a user that isnt the owner to be able to create indexes... I added the user to db_ddladmin, but that gived that user way too much power... can I revoke the other permissions that come with db_ddladmin like DROP, etc?

    Thanks!

    Kindest Regards,

  • March 26, 2004 at 6:18 pm

    #500783

    Look at "GRANT" in SQL Books Online. I gotta run right now but if you don't see any help there I will try to give you a better answer.

  • March 26, 2004 at 6:22 pm

    #500784

    Hey!

    BOL is the first place I looked.  From what I read, it doesnt look like you can GRANT index permissions.

    Thanks for any additional help!

    Kindest Regards,

  • March 26, 2004 at 7:12 pm

    #500786

    Ok verified by testing. Trying to revole DROP returns

    Privilege DROP <object> may not be granted or revoked.

     

    and trying to revoke CREATE INDEX returns

    Privilege CREATE INDEX may not be granted or revoked.

     

    So it is a big no to beign able to do those for sure.

  • March 29, 2004 at 7:56 am

    #500856

    I think you can't assign direct create index to users. The owner of the table or the db owner, can create indexes.

    So like I said beore, you could try creating an stored procedure with parameters like @TableName and @IndexCols, wich create the index in the columns specified.

    I've have never trid it. I'm just guessing.

  • March 29, 2004 at 12:42 pm

    #500924

    Hey!

    Unfortunately I cant try the stored procedure way, as the code is already written to do the indexing, and it currently uses an account that has full permissions to everything (sa).

    Thats why I need to do this, its crazy to use the sa account for everything.  I created an account with as few permissions as possible, and I was intending on switching the application to use this account, but creating indexes is something that the application needs to do... and I cant really change how it does it.

    Thanks guys!

    Kindest Regards,

  • March 29, 2004 at 2:40 pm

    #500946

    I think the only way is to create the index under another security context. This could be a SQL agent job but would you recoding some of your SP.


    Simon Sabin
    SQL Server MVP

    http://sqlblogcasts.com/blogs/simons

  • March 29, 2004 at 2:55 pm

    #500955

    Hey!

    Yeah, I guess we can only do what SQL allows... if we there is no way, then I guess we will have to look at changing what we do for index creation.

    My only concern with the application using another account do the indexing is that doing that pretty much defeats the purpose of adding a low-permission role...

    Thanks again guys!

    Kindest Regards,

  • March 29, 2004 at 2:58 pm

    #500956

    Why do you need to create an index in the first place?


    Simon Sabin
    SQL Server MVP

    http://sqlblogcasts.com/blogs/simons

  • March 29, 2004 at 3:07 pm

    #500960

    Hey!

    They need to create indexes because it is a requirement of the application. 

    There are hundreds of columns of data, but not all of them can be searched with the application... the administrator decides which can be searched, and indexes those columns if they need an index.

    Thanks

    Kindest Regards,

  • March 29, 2004 at 3:20 pm

    #500968

    Sorry to be dumb. So your adminstrator uses the same application to administer the app as the users do. If not why can't the indexes by created outside of the application?


    Simon Sabin
    SQL Server MVP

    http://sqlblogcasts.com/blogs/simons

Viewing 15 posts - 1 through 15 (of 15 total)

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