March 26, 2004 at 11:31 am
March 26, 2004 at 11:42 am
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
March 26, 2004 at 1:22 pm
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
March 26, 2004 at 6:18 pm
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
March 26, 2004 at 7:12 pm
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
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
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!
March 29, 2004 at 2:40 pm
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
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!
March 29, 2004 at 2:58 pm
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
March 29, 2004 at 3:20 pm
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