Solved

SQL 2008 R2 Permissions

Posted on 2013-05-13
1
347 Views
Last Modified: 2013-05-30
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
Comment
Question by:cheryl9063
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 8

Accepted Solution

by:
didnthaveaname earned 500 total points
ID: 39161523
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question