Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL 2008 R2 Permissions

Posted on 2013-05-13
1
Medium Priority
?
367 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 2000 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

609 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