SQL 2008 R2 Permissions

How do you give permissions to add or drop indexes? I do not want the user to be able to truncate tables, drop tables stop replication. I only want this user to be able to login with a special SQL login and add or drop indexes on a table. Please do not suggest I do this myself or ask why I'm allowing this. This is the business requirement period. Can this be done and if so how?
Who is Participating?
didnthaveanameConnect With a Mentor Commented:
Create index:
Requires ALTER permission on the table or view. User must be a member of the sysadmin fixed server role or the db_ddladmin and db_owner fixed database roles.
(src: http://msdn.microsoft.com/en-us/library/ms188783%28v=sql.100%29.aspx)

Drop index: same as create index (src: http://msdn.microsoft.com/en-us/library/ms176118%28v=sql.105%29.aspx

Alternatively, you could look at creating a stored procedure that takes the requisite arguments and creates the index using an execute as clause and give aforementioned permissions to that account instead of the user.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.