• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 378
  • Last Modified:

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?
0
cheryl9063
Asked:
cheryl9063
1 Solution
 
didnthaveanameCommented:
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.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now