Allow only CREATE INDEX on certain tables.

Question

Allow only CREATE INDEX on certain tables.

Paul Cassar

Ten Centuries

Points: 1065
More actions
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,

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

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

Rodrigo Acosta SSC-Insane Points: 21261 More actions · Answer 1

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.

Paul Cassar Ten Centuries Points: 1065 More actions · Answer 2

Well that kind of sucks!

Thanks for the info !

Paul

Kindest Regards,

Antares686 SSC Guru Points: 125444 More actions · Answer 3

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.

Paul Cassar Ten Centuries Points: 1065 More actions · Answer 4

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,

Antares686 SSC Guru Points: 125444 More actions · Answer 5

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.

Paul Cassar Ten Centuries Points: 1065 More actions · Answer 6

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,

Antares686 SSC Guru Points: 125444 More actions · Answer 7

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.

Rodrigo Acosta SSC-Insane Points: 21261 More actions · Answer 8

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.

Paul Cassar Ten Centuries Points: 1065 More actions · Answer 9

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,

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 10

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

Paul Cassar Ten Centuries Points: 1065 More actions · Answer 11

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,

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 12

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

Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons

Paul Cassar Ten Centuries Points: 1065 More actions · Answer 13

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,

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 14

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